In this article
Indexes and storage
What is the difference between a clustered and a nonclustered index?
A clustered index is the structure that stores a table's data rows in the order of its key, so a table can have only one. A nonclustered index is a separate structure holding key values plus a locator for each row, so a table can have many. A table with no clustered index is called a heap. Pick a narrow, stable key for the clustered index and add nonclustered ones for specific queries.
Why it is asked: it tests whether you know how rows are physically found. Read the full explanation: Microsoft Learn, clustered and nonclustered indexes.
How do you read an execution plan, and what does the Query Store do?
An execution plan shows the steps the engine chose for a query: which indexes it scanned or sought, how it joined tables and where most of the work sits. Read it from the costliest operators, and look for scans where you expected seeks. The Query Store is a built-in feature that keeps past plans and runtime statistics, so you can see when a query got slower after a plan changed.
Why it is asked: it separates people who write queries from people who tune them. Read more: SQL interview questions: the complete guide.
What is the difference between a primary key and a unique key?
A primary key identifies each row, allows no NULL and exists once per table. A unique constraint also prevents duplicates but can be added many times and allows a NULL (SQL Server permits one). By default a primary key creates a clustered index when the table has none, and a unique constraint creates a nonclustered one, though both defaults can be changed.
Why it is asked: it tests basic table design. Read the full explanation: Microsoft Learn, create primary keys.
Joins and queries
What is the difference between an inner join and a left join?
An inner join keeps only rows with a match on both sides; a left join keeps every row of the left table and fills the right side with NULL when nothing matches. In the toy data, five orders match customers, so the inner join returns 5 rows, while a left join from the four customers returns 6, because one customer has no orders.1
Why it is asked: joins are the base of almost every query. Read the full explanation: what SQL joins are and how inner, left, right and full joins differ.
How do you find customers who have no orders?
Left join customers to orders and keep rows where the order key is NULL, or use NOT EXISTS with a correlated subquery. Both return the customers with no match. In the toy data exactly one customer, Dana, has no orders.1 Avoid NOT IN against a column that can hold NULL, because one NULL makes the whole comparison return no rows.
Why it is asked: it checks that you can express "absence" safely. Read the full explanation: SQL joins.
What is the difference between WHERE and HAVING?
The WHERE clause filters individual rows before grouping; the HAVING clause filters groups after aggregation, so it can use aggregates such as SUM. In the toy data the totals per customer are 80, 20 and 140; HAVING SUM(amount) > 50 keeps the first and third.1 Filter early with WHERE whenever the condition does not need an aggregate.
Why it is asked: it tests your model of the order in which a query is evaluated. Read more: SQL interview questions: the complete guide.
What is the difference between COUNT(*) and COUNT(column)?
The COUNT(*) form counts rows; COUNT(column) counts only rows where that column is not NULL. After the left join of customers to orders, COUNT(*) returns 6 and counting the order key returns 5, because the unmatched customer has a NULL order key.1
Why it is asked: silent NULL handling is a common source of wrong totals. Read the full explanation: SQL joins.
Does a SELECT without ORDER BY return rows in a stable order?
No. Without ORDER BY the engine makes no promise about row order, and the order you see today can change after an index or plan change. Add ORDER BY whenever order matters, and use OFFSET with FETCH for paging on top of it (Microsoft Learn, the order by clause reference).
Why it is asked: relying on accidental order is a classic bug. Read the full explanation: SQL Order By: how to sort rows ascending and descending.
How do you write a conditional total with case?
Put a CASE expression inside the aggregate, so each row contributes only when its condition holds. For example SUM(CASE WHEN amount >= 50 THEN amount ELSE 0 END) totals only large orders; on the toy data that is 190, from orders of 50, 70 and 70.1 A CASE expression returns one value per row and can be used in SELECT, WHERE and ORDER BY.
Why it is asked: it is the standard pivot-style reporting pattern. Read the full explanation: sum and case when in SQL and case statements in SQL.
When do you use a common table expression instead of a temporary table?
A common table expression (CTE) is a named result defined with WITH that exists only for one statement; it makes a long query readable and supports recursion. A temporary table is stored in tempdb, lives for the session, and can be indexed and reused across many statements. Use a CTE for clarity, and a temporary table when you reuse a large intermediate result.
Why it is asked: it checks whether you choose tools by cost, not habit. Read more: SQL interview questions: the complete guide.
What is the difference between a temporary table and a table variable?
A temporary table is created with a #name table statement, lives in tempdb for the session, can be indexed after creation and normally gets statistics, which helps the optimiser estimate row counts. A table variable is declared with DECLARE @t TABLE, is scoped to its batch or procedure, carries no statistics of its own, and is not undone by a transaction rollback. Prefer a temporary table for large intermediate results.
Why it is asked: it tests whether you know how scope and statistics affect plans. Read the full explanation: Microsoft Learn, the create table reference.
What is the difference between a stored procedure and a user-defined function?
A stored procedure is a saved batch of statements run with EXEC; it can modify data and return several result sets. A user-defined function returns a value or a table and can be used inside a SELECT, but it cannot modify data in tables. Choose a function to reuse a calculation in a query, a procedure to perform an operation.
Why it is asked: it checks whether you know where each can be called. Read the full explanation: Microsoft Learn, user-defined functions and creating a stored procedure.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →Window functions
What do ROW_NUMBER, RANK and DENSE_RANK do differently?
A window function is a function that computes a value for each row from a set of related rows without collapsing them into one row (Microsoft Learn, the over clause reference). For amounts 70, 70, 50, 30, 20 in descending order, ROW_NUMBER gives 1 to 5, RANK gives 1, 1, 3, 4, 5 and DENSE_RANK gives 1, 1, 2, 3, 4.1 They differ only on ties.
Why it is asked: ranking per group is a frequent query task. Read the full explanation: Microsoft Learn, the over clause reference.
How do you calculate a running total?
Use SUM(amount) OVER (ORDER BY order_id), where ORDER BY sets the order of accumulation. On the toy orders the running total is 50, 80, 100, 170, 240.1 To restart the total per group, add PARTITION BY: SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_id) gives 50, 80, 20, 70, 140 on the same five orders.1 When ORDER BY is present without a frame clause, the default frame runs from the first row to the current row and treats ties as peers, so write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit.
Why it is asked: it tests window frames, not just syntax. Read the full explanation: Microsoft Learn, the over clause reference.
Modifying data and transactions
What is the difference between delete and truncate?
The DELETE statement removes rows one by one, accepts a WHERE clause and fires delete triggers. The TRUNCATE TABLE statement removes all rows at once, takes no WHERE, logs far less, resets an IDENTITY counter, fires no delete triggers and cannot run on a table referenced by a foreign key. Contrary to a common belief, TRUNCATE can be rolled back when it runs inside an open transaction (Microsoft Learn, the truncate table reference).
Why it is asked: the rollback myth is a quick check of depth. Read the full explanation: Microsoft Learn, the truncate table reference.
What is a transaction, and what does ACID mean?
A transaction is a group of statements that succeed or fail together, ended by COMMIT or ROLLBACK. Its four properties are called atomicity, consistency, isolation and durability (ACID). Atomicity means all or nothing, consistency means constraints still hold, isolation means concurrent transactions do not see each other's half-finished work, and durability means a committed change survives a crash. A transfer between two accounts is the usual example.
Why it is asked: every data role depends on safe writes. Read the full explanation: transaction control language in SQL.
What are the isolation levels in SQL Server?
An isolation level is a setting that controls how far one transaction is shielded from the uncommitted or changing data of others. SQL Server offers five: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT and SERIALIZABLE (Microsoft Learn, the isolation level statement reference). The default is READ COMMITTED, which prevents dirty reads but allows non-repeatable reads and phantoms. Setting the READ_COMMITTED_SNAPSHOT database option to ON makes READ COMMITTED read row versions, one view per statement, while SNAPSHOT gives one versioned view per transaction.
Why it is asked: it tests concurrency trade-offs. Read the full explanation: Microsoft Learn, isolation levels.
What is a deadlock and how do you reduce it?
A deadlock is a cycle in which sessions each hold a lock another needs, so none can continue; the cycle can involve more than two sessions. The engine picks one session as the victim, rolls it back and returns error 1205 to it (Microsoft Learn, the deadlocks guide); SET DEADLOCK_PRIORITY influences which session is chosen. To reduce deadlocks, access tables in the same order everywhere, keep transactions short, and index the columns that queries filter on so they lock fewer rows.
Why it is asked: it shows whether you have handled real concurrent load. Read the full explanation: transaction control language in SQL.
What does the NOLOCK hint do?
The nolock table hint makes a query read without taking shared locks, so it can return uncommitted data (dirty reads), rows twice or not at all. The isolation-level reference says setting READ UNCOMMITTED has the same effect as applying that hint to every table in every query (Microsoft Learn). Prefer row versioning when readers should not block writers.
Why it is asked: it shows whether you trade correctness for speed knowingly. Read the full explanation: Microsoft Learn, isolation levels.
Newer features
Can SQL Server work as a vector database?
SQL Server 2025 (17.x), Azure SQL Database, Azure SQL Managed Instance and SQL database in Microsoft Fabric have a vector data type for similarity search (Microsoft Learn, Vector data type, read October 2026). You declare a column such as vector(3) and compare vectors with the VECTOR_DISTANCE function. A strong answer compares it with a dedicated vector store: staying in one database keeps transactions and security together, while a specialised engine may scale search differently.
Why it is asked: it probes whether you follow where the product is going. Read the full explanation: Microsoft Learn, vector data type.
Follow-up questions
What are SQL Server interview questions?
SQL Server interview questions are the questions asked of candidates for roles that use Microsoft SQL Server: indexes, joins, aggregation, window functions, transactions and tuning. The ones above are the recurring core. For the general query topics shared by every database, read the SQL interview questions guide.
What are SQL Server data engineer interview questions?
Expect the topics above plus data-loading and design questions: bulk loading with BULK INSERT, incremental loads with MERGE, partitioning large tables, columnstore indexes for analytics, how you choose keys and indexes, and how you load data safely inside a transaction. The page on transaction control language covers the last point.
How should you practise these answers?
Create a small table like the toy orders table, run each query, and check that the result matches what you said. Then say each answer aloud in under a minute, ending with the trade-off. The SQL guide lists the neighbouring topics to cover.
Which of these questions are the most likely to be follow-ups?
After a join question, interviewers often ask about NULL handling and counts; after a transaction question, they move to isolation levels and deadlocks. Practise those pairs together: joins, then transactions.
- A clustered index stores the rows in key order, so a table has one; nonclustered indexes are separate and can be many.
- Use
NOT EXISTSor a left join to find missing matches, and remember thatCOUNT(column)skipsNULL. - Add
ORDER BYwhenever order matters, and use window functions for ranking and running totals. TRUNCATEcan be rolled back inside a transaction; the default isolation level isREAD COMMITTED.- SQL Server 2025 adds a vector data type, so similarity search can run inside the same transactional database.
Sources
- Clustered and nonclustered indexes described (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- SET TRANSACTION ISOLATION LEVEL (Transact-SQL) (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- SELECT - OVER clause (Transact-SQL) (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- Vector data type (Transact-SQL) (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- TRUNCATE TABLE (Transact-SQL) (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- ORDER BY clause (Transact-SQL) (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- User-defined functions (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- Create a stored procedure (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- Deadlocks guide (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- Create primary keys (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
- CREATE TABLE (Transact-SQL) (Microsoft Learn) — Microsoft Learn, 26 Oct 2026
