1. What is a Table?
A Table is an object in the database that contain all the data in a database.
2. What are the Types of Tables?
Partitioned Tables - the data of the partitioned tables is divided horizontally into units spread over one or more filegroups.
Temporary Tables - are stored in tempdb. In SQL Server database there are two types of temporary tables: local and global.
System Tables - returns information about the objects stored in the database.
Wide Tables - use sparse columns to increase the total of columns that a table can have to 30,000.
3. What is a Primary key?
A Primary key is a column or combination of columns that contain values that uniquely identify each row in the table.
4. What is a query ?
A query is the result set of a SELECT statement. The select command is used to retrieve rows and columns from one or more tables.
5. What is a DML statement?
Data Manipulation Language (DML) allow you to retrieve, change or add information in the database. DML statements include the following:
SELECT - query and return rows from table.
INSERT - insert new rows into table.
UPDATE - update existing rows in a table.
DELETE - delete rows from a table.
MERGE - insert, update or delete rows from a table.
BULK INSERT - imports rows from a data file into a table.
6. What is a subquery in T-SQL?
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.
7. What is a join?
Join is a SQL keyword used retrieve rows from two or more tables based on the relationship between the columns of the tables.
8. What are the types of join in T-SQL?
Inner join - is used in a query to returns rows when a match is found.
Left outer join - returns all rows from the left table.
Right outer join - returns all rows from the right table.
Full outer join - is used in a query to returns rows from both the left and right tables.
Cross join - returns the Cartesian product of rows from the rowsets in the join.
9. What is the difference between UNION and UNION ALL?
UNION returns only distinct values, eliminate duplicate rows.
UNION ALL will not eliminate duplicate rows, returns all values.
10. What is the difference between Char and Varchar Data Type?
The Char type uses the entire declared length even if fewer characters are stored in the variable. Practically loads empty spaces.
The Varchar type does not use the entire declared length, it uses only the length loaded with characters.
11. What is an Index?
Index helps to search for information faster in a database when using a simple select in a table or a complex query.
12. What are the types of indexes in T-SQL?
Clustered - store data in a specific order.
Nonclustered - the data is stored in a random order.
Unique - does not allow rows with duplicate values.
Filtered - is an optimized disk-based rowstore nonclustered index.
Columnstore - uses column-based data storage.
Hash - data is accessed through an in-memory hash table.
Memory-Optimized Nonclustered - store data in a memory-optimized table.
XML - the data is stored on xml data type columns.
Spatial - allows you to index a spatial column.
13. What is a view?
A view is a virtual table based on a SELECT statement of one or more tables.
14. What are the types of views in T-SQL?
Indexed Views - is a view that has been materialized.
Partitioned Views - joins horizontally partitioned data from a set of tables existing on one or more servers.
System Views - return information about the objects defined in the SQL Server instance.
15. What is a trigger?
A trigger is a special type of stored procedure that is fired when a specific type of data modification is made against a specific table or column.
16. What are the Types of DML Triggers?
AFTER trigger - are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed.
INSTEAD OF trigger - override the standard actions of the triggering statement.
17. What are the types of functions in T-SQL?
Scalar Function - returns a single value.
Table-Valued Functions - return a table data type.
System Functions - are groups functions like Metadata functions, Security functions, Trace functions.
18. What is a synonym?
A synonym is a database object that provides an alternative name for another database object, referred to as the base object.
19. What is a DDL statement?
Data Definition Language (DDL) allow you to defines objects in a database. DDL statements include the following:
CREATE - create new database objects: table, view, index, trigger, function or procedure.
ALTER - modify the structure of existing objects in a database.
DROP - remove existing objects from the database.
RENAME - renames a table name, column name.
TRUNCATE TABLE - delete all rows from a table.
DISABLE TRIGGER - is used to disable a trigger.
ENABLE TRIGGER - is used to enable a trigger.
COLLATIONS - defines a collation of a database or table column.
UPDATE STATISTICS - updates query optimization statistics on a table or indexed view.