SQL Server Interview Questions and Answers
Basic Level Questions
Q: What is SQL Server?
A: SQL Server is a relational database management system (RDBMS) developed by Microsoft that stores and retrieves
data as requested by other software applications.
Q: What are the different types of JOINs in SQL Server?
A: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, SELF JOIN.
Q: What is the difference between DELETE, TRUNCATE, and DROP?
A: DELETE: Removes rows, can rollback, affects only data.
TRUNCATE: Removes all rows, cannot rollback, resets identity.
DROP: Deletes the table completely including schema.
Q: What is a primary key?
A: A primary key uniquely identifies each record in a table and does not allow NULL values.
Q: What is the difference between a clustered and a non-clustered index?
A: Clustered index sorts and stores rows in table; non-clustered index stores pointers to rows.
Intermediate Level Questions
Q: What is normalization? Explain its types.
A: Normalization organizes data to reduce redundancy.
1NF: Atomic columns, 2NF: Remove partial dependencies, 3NF: Remove transitive dependencies, BCNF: Stronger
3NF.
Q: What is a view in SQL Server?
A: A view is a virtual table based on the result of a SELECT query.
Q: What are stored procedures?
A: Precompiled set of SQL statements for performance and reuse.
Q: How do you handle exceptions in SQL Server?
A: Use TRY...CATCH blocks to catch and handle errors.
Q: What is a CTE?
A: A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a query.
SQL Server Interview Questions and Answers
Advanced Level Questions
Q: What is indexing and how does it impact performance?
A: Indexes improve read performance but may slow down write operations.
Q: What is a deadlock? How to resolve it?
A: A deadlock is when two sessions wait on each other's resources. Resolve by optimizing queries and minimizing
locking.
Q: What is the difference between RAISERROR and THROW?
A: RAISERROR is older, THROW is newer (SQL 2012+), simpler and preferred for error handling.
Q: What is the difference between temp tables and table variables?
A: Temp tables support transactions and statistics; table variables are scoped and lightweight.
Q: Explain replication in SQL Server.
A: Replication copies data between databases. Types: Snapshot, Transactional, Merge.
Q: What are SQL Server isolation levels?
A: Read Uncommitted, Read Committed, Repeatable Read, Serializable, Snapshot - control transaction visibility.
Q: What is SQL Server Agent?
A: It automates jobs like backups and tasks.
Q: What are triggers? Types?
A: Triggers are automatic responses to events. Types: DML (AFTER, INSTEAD OF), DDL.
Q: What is partitioning in SQL Server?
A: Partitioning splits a large table into smaller parts for better performance.
Q: How do you optimize a slow-performing SQL query?
A: Use execution plans, avoid SELECT *, use indexing, simplify queries, reduce subqueries.
