Microsoft SQL Server continues to be one of the most widely used relational database platforms in the industry. Whether you are preparing for roles such as SQL Developer, MSBI Developer, Data Engineer, Database Administrator (DBA), or Support Engineer, this comprehensive set of interview questions will help you cover all fundamental to advanced concepts.
This guide is structured topic-wise for easier learning and quick revision.
1. Basic SQL Concepts
1. What is SQL Server?
A relational database management system (RDBMS) developed by Microsoft that supports transaction processing, analytics, and business intelligence workloads.
2. What are tables, rows, and columns?
-
Table: Structured set of data.
-
Rows: Records in a table.
-
Columns: Attributes of the data.
3. What are Primary Keys?
A unique identifier for each row in a table. Cannot contain NULL values.
4. What is a Foreign Key?
A field that establishes a relationship between two tables.
5. What is a Unique Key?
Ensures all values in the column are distinct; allows a single NULL.
2. SQL Queries (DML / DQL)
6. Difference between WHERE and HAVING?
-
WHERE: Filters rows before grouping.
-
HAVING: Filters groups after aggregation.
7. What is a JOIN? Name different types.
Combines rows from multiple tables.
Types: INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF JOIN.
8. What is a Subquery?
A query within another query.
9. Write a query to find the second highest salary.
10. How do you find duplicate records?
3. DDL Commands
11. What is the difference between TRUNCATE and DELETE?
-
DELETE: Logs each row, supports WHERE, slower.
-
TRUNCATE: Removes all rows, minimal logging, resets identity.
12. What is the ALTER command used for?
Used to modify an existing table schema (add/modify/drop columns).
4. Constraints
13. Types of Constraints?
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
14. What is a CHECK constraint?
Ensures values meet a condition.
5. Normalization & Schema Design
15. What is Normalization?
A process to organize data to reduce redundancy and improve data integrity.
16. Name different Normal Forms.
1NF, 2NF, 3NF, BCNF, 4NF, 5NF.
17. What is Denormalization?
Introducing redundancy to improve read performance.
6. Transactions & ACID Properties
18. Define ACID properties.
Atomicity, Consistency, Isolation, Durability.
19. What is a Transaction?
A logical unit of work executed as a single operation.
20. What are Isolation Levels?
Read Uncommitted, Read Committed, Repeatable Read, Serializable, Snapshot.
7. Functions (Scalar & Aggregate)
21. Difference between Scalar & Aggregate functions?
-
Scalar: Returns a single value.
-
Aggregate: Works on a set of rows.
22. Example of Scalar Function?
LEN(), UPPER(), GETDATE()
23. Example of Aggregate Function?
SUM(), AVG(), MAX(), COUNT()
8. Stored Procedures, Functions, Views, Triggers
24. What is a Stored Procedure?
Precompiled set of SQL statements that can accept parameters.
25. What is the difference between Function and Stored Procedure?
| Function | Stored Procedure |
|---|---|
| Must return value | Optional return |
| Cannot modify DB state | Can modify DB |
| Can be used in SELECT | Cannot be used in SELECT |
26. What is a View?
A virtual table built from a query.
27. What is a Trigger?
Automatic execution of SQL code when an event occurs (Insert/Update/Delete).
9. Indexing (Clustered & Non-Clustered)
28. What is an Index?
Improves query performance by optimizing search operations.
29. Difference between Clustered and Non-Clustered Index?
-
Clustered: Defines physical order of data. One per table.
-
Non-Clustered: Logical order stored separately. Many per table.
30. What is a Covering Index?
An index containing all columns required by a query.
10. Performance Tuning
31. How do you analyze slow queries?
Using execution plans, SET STATISTICS IO/TIME, indexing analysis.
32. What are Execution Plans?
A graphical/text representation showing how SQL Server executes a query.
33. What causes Table Scans?
Missing indexes, poor filter conditions.
34. How do you reduce fragmentation?
Rebuild or reorganize indexes.
11. Joins – Advanced Questions
35. What is the difference between INNER and OUTER JOIN?
-
INNER: Returns matching rows only.
-
OUTER: Returns matching + non-matching rows.
36. What is CROSS APPLY vs OUTER APPLY?
Used with table-valued functions to join with correlated queries.
12. SQL Server Architecture
37. What is SQL Server Agent?
Used for scheduling jobs (backups, scripts, maintenance).
38. What is TempDB?
A system database used for temporary objects and sorts.
39. What is a Checkpoint?
Process of writing dirty pages from buffer cache to disk.
13. Backups & Recovery
40. Types of Backups?
Full, Differential, Transaction Log, File/Filegroup.
41. What is Point-in-Time Recovery?
Restoring the database to a specific moment using T-Logs.
14. Security
42. What is Authentication vs Authorization?
-
Authentication: Identity verification.
-
Authorization: Access rights.
43. What are SQL Server Roles?
Server-level and database-level predefined roles for access control.
15. Advanced Topics
44. What is Partitioning?
Splitting a large table into smaller pieces.
45. What is Replication?
Copying and distributing data across servers.
46. What is AlwaysOn Availability Group?
High availability and disaster recovery feature.
47. What is SQL Profiler?
Tool for tracing SQL Server events.
48. What is Deadlock?
Two processes waiting on each other’s resources.
49. How do you detect Deadlocks?
Using SQL Profiler, Extended Events, or by reviewing error logs.
50. How do you resolve Deadlocks?
Optimize queries, add indexes, ensure consistent locking order.