SQL Server Interview Questions and Answers
SQL Server interview questions for .NET developers — joins, indexes (clustered vs non-clustered), execution plans, transactions and isolation, and query tuning.
102Questions
12Topics
Basic to AdvancedDifficulty
Fresher to 5+ YrsExperience
- 01 What is the OUTPUT clause and when do you use it? OUTPUT returns the rows affected by an INSERT/UPDATE/DELETE/MERGE, exposing the inserted and deleted pseudo-tables.…
- 02 What is the MERGE statement, and what are its pitfalls? MERGE does an upsert in one statement: WHEN MATCHED THEN UPDATE, WHEN NOT MATCHED THEN INSERT, optionally WHEN NOT…
- 03 What is the difference between SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT()? SCOPE_IDENTITY() — last identity generated in the current scope (your statement/proc); this is the safe choice.…
- 04 What is a SEQUENCE, and how is it different from IDENTITY? A SEQUENCE is a standalone schema object that hands out numbers via NEXT VALUE FOR dbo.MySeq. Unlike IDENTITY, it's…
- 05 What is a computed column, and when should it be persisted? A computed column's value comes from an expression over other columns in the row (FullName AS FirstName + ' ' +…
- 06 What are window functions, and how do they differ from GROUP BY? A window function computes an aggregate or ranking across a set of rows related to the current row (the “window”,…
- 07 What is the difference between ROW_NUMBER, RANK and DENSE_RANK? All assign a number over an ordered window. ROW_NUMBER() — always unique (1,2,3,4), ties broken arbitrarily. RANK() —…
- 08 What is a recursive CTE, and what is it used for? A recursive CTE references itself to walk hierarchical or graph data — an org chart, a category tree, a bill of…
- 09 What is tempdb used for, and why can it be a bottleneck? tempdb is a shared system database used for temp tables and table variables, sort/hash spills when a query needs more…
- 10 What is the plan cache, and how does plan reuse work? The plan cache stores compiled execution plans so repeated queries skip the expensive optimization step. Parameterised…
- 11 What is an indexed view (materialized view) in SQL Server? A normal view is just a saved query — it stores no data. An indexed view has a unique clustered index, so its result…
- 12 How do you paginate query results efficiently in SQL Server? ORDER BY ... OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY is the simple approach, fine for early pages. For deep…
- 13 What is a table-valued parameter, and when do you use one? A TVP lets you pass a whole set of rows into a stored procedure or function as a single parameter, typed by a…
- 14 What is a read replica, and when should you use one? A read replica is a copy of the primary database kept in sync (asynchronously) that serves read-only queries. You…
- 15 What is the difference between OLTP and OLAP workloads? OLTP — many small, fast, concurrent reads/writes; highly normalized; rowstore; tuned for integrity and concurrency…
- 16 What is the difference between a clustered and a nonclustered columnstore index? A clustered columnstore index is the table's storage — the whole table stored column-wise; best for a large…
- 17 What is the difference between a correlated and an uncorrelated subquery? An uncorrelated subquery is independent — it can run on its own, once, and its result is used by the outer query. A…
- 18 What is Change Data Capture (CDC) in SQL Server? CDC records every insert, update and delete made to a table by reading the transaction log, and exposes them as…
- 19 What is a linked server in SQL Server? A linked server is a registered connection to another data source (another SQL Server, or via OLE DB — Oracle, an…
- 20 What is In-Memory OLTP in SQL Server? In-Memory OLTP (codename Hekaton) lets you declare memory-optimized tables that live entirely in RAM with row…
- 21 What is a full-text index in SQL Server? A full-text index tokenises text columns (word-breaking, stemming, stop words) so you can search for words and phrases…
- 22 What is a database snapshot in SQL Server? A database snapshot is a read-only, point-in-time view of a database, implemented as a sparse file that copies only…
- 23 What is the difference between a natural key and a surrogate key? A natural key is a column (or set) that already uniquely identifies a row in the real world — an email, an ISBN, a…
- 24 What is the difference between putting a condition in the JOIN's ON clause versus the WHERE clause? For an INNER JOIN it makes no difference — filtering in ON or WHERE gives the same result. For an OUTER JOIN it…
- 25 What is the difference between a subquery, a derived table and a CTE? A subquery is a query nested in a SELECT/WHERE/HAVING. A derived table is a subquery in the FROM clause with an alias.…
- 26 For checking whether any rows exist, should you use COUNT or EXISTS? Use EXISTS (or EF's .Any()). EXISTS stops at the first matching row; COUNT(*) > 0 counts every matching row before you…
- 27 What is the difference between WHERE and HAVING? WHERE filters rows before grouping and can't use aggregate functions. HAVING filters groups after GROUP BY and is…
- 28 What is the difference between UNION and UNION ALL? UNION combines two result sets and removes duplicate rows, which requires a sort or hash — extra work. UNION ALL just…
- 29 What is a Common Table Expression (CTE), and when would you use one? A CTE is a named, temporary result set defined with WITH that exists only for the following statement. It makes…
- 30 What are window functions (ROW_NUMBER, RANK, DENSE_RANK)? Window functions compute a value across a set of rows (“window”) related to the current row, without collapsing them…
- 31 What is the difference between a temp table, a table variable and a CTE? #temp table — a real table in tempdb; has statistics and indexes, participates in transactions, best for larger sets…
- 32 What is database normalization (1NF, 2NF, 3NF)? Normalization organises tables to remove redundant data and update anomalies. 1NF — atomic values, no repeating…
- 33 What are the SQL Server transaction isolation levels? Isolation levels trade consistency for concurrency by controlling which anomalies you can see: Read Uncommitted (dirty…
- 34 What is a deadlock in SQL Server, and how do you prevent it? A deadlock is when two transactions each hold a lock the other needs, so neither can proceed. SQL Server's deadlock…
- 35 What is the difference between CHAR, VARCHAR and NVARCHAR? CHAR(n) is fixed length — always n bytes, padded with spaces. VARCHAR(n) is variable length — 1 byte per character…
- 36 What is the difference between ISNULL and COALESCE? ISNULL(a, b) is a T-SQL function taking exactly two arguments; the result type follows the first argument. COALESCE(a,…
- 37 What is the difference between EXISTS and IN? EXISTS checks whether a subquery returns any row and stops at the first match — a semi-join. IN compares a value…
- 38 How do you paginate results in SQL Server, and what is keyset pagination? ORDER BY … OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY is the simple approach, but it gets slow for deep pages…
- 39 What is a trigger in SQL Server, and when should you avoid one? A trigger is code that runs automatically in response to INSERT, UPDATE, DELETE (DML triggers) or DDL/logon events.…
- 40 What is SQL, and what are DDL, DML, DCL and TCL? SQL (Structured Query Language) is the standard language for relational databases. Its statements group into four…
- 41 What is the logical order of execution of a SQL SELECT query? Even though you write SELECT first, SQL Server logically processes: FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT →…
- 42 What is NULL in SQL, and what is three-valued logic? NULL means “unknown”, not zero or empty string. Any comparison with NULL yields UNKNOWN — so x = NULL is never true;…
- 43 What is a view, and what is an indexed view? A view is a saved SELECT query you can treat like a table. It stores no data — it runs its query each time you use it.…
- 44 What is the difference between a stored procedure and a user-defined function? A function must return a value (scalar or a table), can't have side effects (no INSERT/UPDATE/DELETE on base tables),…
- 45 What is a self join, and when would you use one? A self join is joining a table to itself, using two different aliases. It's used when rows in a table relate to other…
- 46 What is a CROSS JOIN? A CROSS JOIN returns the Cartesian product — every row of the first table paired with every row of the second (M × N…
- 47 What is a FULL OUTER JOIN? A FULL OUTER JOIN returns all matched rows, plus unmatched rows from both sides (with NULLs on the missing side). It's…
- 48 What are CROSS APPLY and OUTER APPLY? APPLY joins a table to a table expression that can reference columns from the outer row — a correlated join. CROSS…
- 49 What is a correlated subquery? A correlated subquery references a column from the outer query, so it's evaluated once per outer row (conceptually). A…
- 50 How do you find the Nth highest salary (or value) in SQL? The cleanest way is a window function: DENSE_RANK() OVER (ORDER BY Salary DESC) in a CTE, then WHERE rnk = @N.…
- 51 How do you find duplicate rows in a table? Group by the columns that define a duplicate and keep groups with more than one row: SELECT Email, COUNT(*) FROM Users…
- 52 How do you delete duplicate rows, keeping one copy? Use ROW_NUMBER() in a CTE partitioned by the duplicate key, then DELETE FROM cte WHERE rn > 1. SQL Server lets you…
- 53 What is a composite index, and does column order matter? A composite index covers multiple columns in a defined order. Order matters a lot: the index is only useful for…
- 54 What is a covering index? A covering index contains every column a query needs — in the key or in the INCLUDE list — so the query can be…
- 55 What is a key lookup (or RID lookup) in an execution plan? A key lookup happens when a non-clustered index seek finds the rows but doesn't contain all the columns the query…
- 56 What is the difference between an index scan and an index seek? A seek navigates the B-tree directly to the rows that match a predicate — efficient when the filter is selective. A…
- 57 What does SARGable mean, and why does it matter? SARGable (“Search ARGument able”) means a predicate can use an index seek. Wrapping the column in a function…
- 58 What is index fragmentation, and how do you fix it? Fragmentation is when index pages are out of physical order (external) or partly empty (internal), usually from page…
- 59 What are statistics in SQL Server, and why do they matter? Statistics are metadata about the distribution of values in a column or index — a histogram plus density info. The…
- 60 What is parameter sniffing? Parameter sniffing is SQL Server compiling a stored procedure's plan based on the parameter values from the first…
- 61 What is a cursor in T-SQL, and why should you usually avoid it? A cursor processes a result set one row at a time in a loop — the opposite of SQL's set-based nature. It's usually…
- 62 What is set-based thinking in SQL? Set-based means describing what result you want as an operation over whole sets of rows, and letting the engine work…
- 63 What is the MERGE statement (upsert) in SQL Server? MERGE combines INSERT, UPDATE and DELETE into one statement based on whether rows from a source match a target: WHEN…
- 64 What are PIVOT and UNPIVOT in T-SQL? PIVOT rotates rows into columns — turning a list of (Month, Amount) rows into one row with a column per month. UNPIVOT…
- 65 What is dynamic SQL, and what are its risks? Dynamic SQL is building a SQL statement as a string at run time and executing it. It's needed for genuinely dynamic…
- 66 What is SQL injection, and how do you prevent it? SQL injection is when attacker-controlled input is concatenated into a SQL string and changes the query's meaning —…
- 67 What is a foreign key, and what is referential integrity? A foreign key is a column (or set) in one table that references the primary/unique key of another, enforcing that…
- 68 What is a CHECK constraint and a DEFAULT constraint? A CHECK constraint enforces a Boolean rule on a row's values — CHECK (Price >= 0), CHECK (EndDate > StartDate). A…
- 69 What is the difference between a UNIQUE constraint and a UNIQUE index? They enforce the same rule and are implemented the same way — a UNIQUE constraint creates a unique index behind the…
- 70 What is a filtered index? A filtered index has a WHERE clause, so it only indexes the subset of rows that match — CREATE INDEX IX_Orders_Open ON…
- 71 What is the difference between an IDENTITY column and a SEQUENCE? IDENTITY is a column property that auto-generates increasing values on insert, scoped to that one table. SEQUENCE is a…
- 72 What are the main lock types in SQL Server (shared, exclusive, update, intent)? Shared (S) — for reads; multiple readers can hold it. Exclusive (X) — for writes; incompatible with everything. Update…
- 73 What is the difference between blocking, locking and deadlocking? Locking is normal and constant — it's how SQL Server maintains isolation. Blocking is when one session must wait for…
- 74 What does WITH (NOLOCK) do, and why is it dangerous? WITH (NOLOCK) sets Read Uncommitted for that table — it reads without taking shared locks and ignores exclusive locks.…
- 75 What is Read Committed Snapshot Isolation (RCSI)? RCSI is a database-level option that changes the default Read Committed isolation to use row versioning instead of…
- 76 How do you handle errors in T-SQL (TRY/CATCH, THROW, RAISERROR)? Wrap the work in BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH. In the CATCH block, roll back if a transaction is…
- 77 What is a savepoint in a transaction? A savepoint marks a point inside a transaction you can partially roll back to without abandoning the whole…
- 78 What is the difference between COUNT(*), COUNT(1) and COUNT(column)? COUNT(*) and COUNT(1) count rows and are identical in performance (the optimiser treats them the same). COUNT(column)…
- 79 What is a computed column in SQL Server? A computed column is defined by an expression over other columns in the same row — FullName AS FirstName + ' ' +…
- 80 What is tempdb used for in SQL Server? tempdb is a shared, recreated-on-restart system database used for anything temporary: user #temp tables and table…
- 81 What is a columnstore index, and when would you use one? A columnstore index stores data column-by-column instead of row-by-row, with heavy compression and batch-mode…
- 82 What is table partitioning in SQL Server? Partitioning splits one large table (and its indexes) horizontally into partitions based on a key range — typically a…
- 83 What backup types does SQL Server have, and what is point-in-time recovery? Three backup types: Full (the whole database), Differential (changes since the last full), and Transaction Log (all…
- 84 What is a schema in SQL Server, and how is it different from a database? A database is the container of all objects and its own storage and transaction log. A schema is a namespace within a…
- 85 How do you concatenate row values into a single string in SQL Server? Use STRING_AGG(column, ', ') (SQL Server 2017+) — the modern, readable way, with an optional WITHIN GROUP (ORDER BY…
- 86 What is the difference between DATETIME and DATETIME2? DATETIME is the legacy type: range from 1753, precision rounded to ~3.33 ms, 8 bytes. DATETIME2 is the modern…
- 87 What is a heap table, and when is a table without a clustered index acceptable? A heap is a table with no clustered index — rows are stored in no particular order and located by a Row ID (RID).…
- 88 Is the primary key always the clustered index? No. The primary key gets a clustered index by default, but you can declare PRIMARY KEY NONCLUSTERED and cluster the…
- 89 What is the difference between optimistic and pessimistic concurrency control? Pessimistic — lock the row when you read it so nobody else can change it until you're done (SELECT ... WITH (UPDLOCK,…
- 90 What is collation in SQL Server? Collation is the set of rules for sorting and comparing character data — case sensitivity, accent sensitivity, and the…
- 91 What are the ACID guarantees, and how does the transaction log provide them? The transaction log records every change before the data pages are written to disk (write-ahead logging). This is what…
- 92 What is an index in SQL Server? An index is an on-disk structure (a B-tree) that lets SQL Server find rows without scanning the whole table. It stores…
- 93 What is the difference between a clustered and a non-clustered index? A clustered index determines the physical order of the rows in the table — the leaf level of the index is the table…
- 94 When should you create an index? Index columns that appear in WHERE, JOIN, ORDER BY and GROUP BY clauses of your frequent, performance-sensitive…
- 95 Can indexes hurt performance? Yes. Every non-clustered index is extra data that must be updated on every INSERT, UPDATE (of an indexed column) and…
- 96 What is an execution plan in SQL Server? An execution plan is the set of physical operations the query optimiser chose to run a query — which indexes to use,…
- 97 How do you optimise a slow SQL query? Measure first: capture the actual execution plan and STATISTICS IO/TIME. Then look for the usual culprits — a scan…
- 98 What is the difference between an INNER JOIN and a LEFT JOIN? An INNER JOIN returns only rows that have a match in both tables. A LEFT JOIN (left outer join) returns every row from…
- 99 What is the difference between a primary key and a unique key? Both enforce uniqueness. A primary key uniquely identifies a row, does not allow NULL, and there is exactly one per…
- 100 What is the difference between DELETE, TRUNCATE and DROP? DELETE is DML: removes rows (optionally with a WHERE), logs each row, fires triggers, keeps the table and can be…
- 101 What is a transaction and what does ACID mean? A transaction is a unit of work that either fully completes or has no effect. ACID is the set of guarantees: Atomicity…
- 102 What is a stored procedure and when would you use one? A stored procedure is a named, precompiled batch of T-SQL saved in the database that you call with EXEC, optionally…
No questions match those filters
Try removing a filter or .
About these SQL Server questions
Almost every .NET role includes SQL Server questions, because most bugs that reach production are data bugs. Interviewers focus on indexes — what a clustered index is, how a non-clustered index points back to the data, when an index helps and when it slows writes — and on reading an execution plan to explain why a query is slow.
The other big themes are joins (and what a missing join predicate does), transactions and isolation levels, and set-based thinking versus row-by-row cursors. The Index chain in this section follows a very common interview sequence from “what is an index?” to “how would you tune this query?”.