SQL Server Interview Questions for .NET Developers
The SQL Server topics that come up in .NET interviews — joins, clustered vs non-clustered indexes, execution plans, transactions and isolation, and tuning a slow query.
You do not need to be a DBA, but every .NET role expects you to reason about indexes, read an execution plan and explain a join. This collection includes the full SQL Server index follow-up chain.
Queries & joins
3 questionsAn INNER JOIN returns only rows that have a match in both tables. A LEFT JOIN (left outer join) returns every row from the left table, plus matching columns from the right table where they exist and NULLs…
Read detailed answerDELETE is DML: removes rows (optionally with a WHERE), logs each row, fires triggers, keeps the table and can be rolled back. TRUNCATE is DDL: removes all rows by deallocating pages, minimally logged, very…
Read detailed answerA stored procedure is a named, precompiled batch of T-SQL saved in the database that you call with EXEC, optionally passing parameters. It centralises data logic, is parameterised (so it resists SQL…
Read detailed answerIndexes (the follow-up chain)
4 questionsAn index is an on-disk structure (a B-tree) that lets SQL Server find rows without scanning the whole table. It stores the indexed column values in sorted order with pointers to the matching rows, so lookups,…
Read detailed answerA clustered index determines the physical order of the rows in the table — the leaf level of the index is the table data — so there can be only one. A non-clustered index is a separate structure; its leaf…
Read detailed answerIndex columns that appear in WHERE, JOIN, ORDER BY and GROUP BY clauses of your frequent, performance-sensitive queries — especially selective columns (those that filter to a small fraction of rows). Index…
Read detailed answerYes. Every non-clustered index is extra data that must be updated on every INSERT, UPDATE (of an indexed column) and DELETE, so too many indexes slow writes and increase locking, logging and storage. Indexes…
Read detailed answerTuning & transactions
4 questionsAn execution plan is the set of physical operations the query optimiser chose to run a query — which indexes to use, in what order to join tables, whether to seek or scan, how to sort and aggregate. The…
Read detailed answerMeasure first: capture the actual execution plan and STATISTICS IO/TIME. Then look for the usual culprits — a scan where a seek should happen (missing or unusable index), non-sargable predicates (functions on…
Read detailed answerA transaction is a unit of work that either fully completes or has no effect. ACID is the set of guarantees: Atomicity (all-or-nothing), Consistency (constraints hold before and after), Isolation (concurrent…
Read detailed answerBoth enforce uniqueness. A primary key uniquely identifies a row, does not allow NULL, and there is exactly one per table (it creates a clustered index by default). A unique key/constraint also guarantees…
Read detailed answer