T-SQL Interview Questions and Answers

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
Temporary Tables
System Tables
Wide Tables

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
INSERT
UPDATE
DELETE
MERGE
BULK INSERT
TRUNCATE 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
Outer join
Left outer join
Right outer join
Full outer join
Cross 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
Nonclustered
Unique
Filtered
Columnstore
Hash
Memory-Optimized Nonclustered
XML
Spatial

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
Partitioned Views
System Views

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
INSTEAD OF trigger

17. What are the types of functions in T-SQL?

Scalar Function
Table-Valued Functions
System 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.