Top 20 SQL Server Interview Questions and Answers

1. What is SQL Server?

Answer: SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store and manage data, and it uses Structured Query Language (SQL) for managing and querying data.

2. What are the different types of joins in SQL Server?

Answer: SQL Server supports several types of joins to combine data from two or more tables:

• Inner Join: Returns records that have matching values in both tables.
• Left Join: Returns all records from the left table and matched records from the right table.
• Right Join: Returns all records from the right table and matched records from the left table.
• Full Outer Join: Returns all records when there is a match in either left or right table.
• Cross Join: Returns the Cartesian product of both tables (every combination of rows).

3. What is normalization in SQL Server?

Answer: Normalization is the process of organizing data in a database to avoid redundancy and dependency. The goal is to ensure that the database is free from insertion, update, and deletion anomalies. It involves dividing large tables into smaller, manageable tables and defining relationships between them.

4. What are indexes in SQL Server?

Answer: Indexes are database objects that speed up the retrieval of rows from a table. They can be created on one or more columns of a table. There are two main types of indexes in SQL Server:

• Clustered Index: Determines the physical order of data rows in the table.
• Non-Clustered Index: Creates a separate structure from the data table and contains pointers to the data.

5. What is a stored procedure in SQL Server?

Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. Stored procedures are used to encapsulate logic and improve performance by reducing network traffic.

6. What is a trigger in SQL Server?

Answer: A trigger is a special type of stored procedure that automatically executes when an event such as an INSERT, UPDATE, or DELETE occurs on a specified table or view. Triggers can be used for enforcing business rules or for logging.

7. What is the difference between UNION and UNION ALL?

Answer: Both UNION and UNION ALL are used to combine results from two or more queries:

• UNION: Removes duplicate rows from the result set.
• UNION ALL: Includes all rows from the result set, including duplicates.

8. What is a primary key?

Answer: A primary key is a column (or combination of columns) that uniquely identifies each row in a table. It must contain unique values and cannot contain NULL values. Each table can have only one primary key.

9. What is a foreign key?

Answer: A foreign key is a column (or combination of columns) used to establish a relationship between two tables. It refers to the primary key of another table and ensures referential integrity between the two tables.

10. What is a view in SQL Server?

Answer: A view is a virtual table that is defined by a query. It can be used to simplify complex queries, aggregate data, or provide a security layer by restricting access to specific columns or rows.

11. What is a subquery?

Answer: A subquery is a query nested inside another query. It can be used to perform operations such as filtering or calculating values. Subqueries can be classified as correlated or non-correlated depending on whether they depend on the outer query.

12. What is a transaction in SQL Server?

Answer: A transaction is a sequence of one or more SQL operations that are executed as a single unit. Transactions ensure data consistency and integrity by adhering to the ACID properties: Atomicity, Consistency, Isolation, and Durability.

13. What are the ACID properties?

Answer: ACID stands for the four key properties of a transaction:

• Atomicity: Ensures that a transaction is fully completed or fully rolled back.
• Consistency: Ensures that a transaction brings the database from one valid state to another.
• Isolation: Ensures that concurrent transactions do not interfere with each other.
• Durability: Ensures that once a transaction is committed, its effects are permanent, even in the case of a system failure.

14. What is the difference between DELETE and TRUNCATE?

Answer: Both DELETE and TRUNCATE are used to remove data from a table, but:

• DELETE: Removes rows one by one and logs each row removal. It can be rolled back and can have conditions applied.
• TRUNCATE: Removes all rows from the table without logging individual row deletions. It cannot be rolled back and does not fire triggers.

15. What is an index scan and index seek?

Answer: An index scan is when SQL Server reads all the rows in an index to satisfy a query, while an index seek is when SQL Server uses the index to directly access the rows that satisfy the query's conditions.

16. What are the differences between a clustered index and a non-clustered index?

Answer: A clustered index determines the physical order of data rows, while a non-clustered index creates a separate structure that contains pointers to the data rows. A table can have only one clustered index but multiple non-clustered indexes.

17. What is a deadlock in SQL Server?

Answer: A deadlock occurs when two or more sessions are blocking each other and cannot proceed because each session is waiting for a resource held by another. SQL Server automatically detects and resolves deadlocks by terminating one of the transactions.

18. What is the purpose of the WITH (NOLOCK) hint?

Answer: The WITH (NOLOCK) hint allows SQL Server to read data without acquiring locks, which can improve performance. However, it can lead to dirty reads, where uncommitted changes may be read.

19. What are common SQL Server data types?

Answer: Common data types in SQL Server include:

• INT: Stores integer values.
• VARCHAR: Stores variable-length character strings.
• DATETIME: Stores date and time values.
• DECIMAL: Stores fixed-point numbers.
• BIT: Stores Boolean values (0 or 1).

20. What is a CTE (Common Table Expression)?

Answer: A CTE is a temporary result set defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are used to simplify complex queries and make the code more readable.