SQL Server and T-SQL Interview Questions for .NET Full Stack
25 SQL Server and T-SQL questions with answers for .net full stack roles — 10 easy, 9 medium, 6 hard.
About SQL Server and T-SQL
Relational database skills every .NET developer needs: keys, joins, indexes, normalisation, stored procedures, functions, CTEs, window functions, transactions and isolation levels. You will practise common query problems and learn to read execution plans, handle deadlocks and parameter sniffing, and manage large tables that power business applications.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is the difference between a primary key and a unique key in SQL Server?
Both enforce uniqueness. A table can have only one primary key, it cannot contain NULLs, and by default SQL Server creates a clustered index for it. A table can have many unique constraints, each allowing a single NULL in SQL Server, and they create non-clustered indexes by default. Use the primary key for row identity, such as EmployeeId, and unique keys for business rules like a unique email or PAN.
INNER JOIN returns rows that match in both tables. LEFT JOIN returns all rows from the left table plus matches from the right, with NULLs where there is no match. RIGHT JOIN is the mirror. FULL OUTER JOIN returns all rows from both sides. CROSS JOIN returns every combination. A self join joins a table to itself, such as employees to their managers. LEFT JOIN with WHERE right.Id IS NULL finds unmatched rows.
03
Write a SQL Server query to find the second highest salary.
-- using DENSE_RANK (handles ties)
SELECT Salary
FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
FROM Employees
) t
WHERE rnk = 2;
-- using OFFSET
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;
Change 2 to N for the Nth highest salary.
04
Write a query to find duplicate email addresses and delete the extra rows, keeping the lowest Id.
-- find duplicates
SELECT Email, COUNT(*) AS Cnt
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;
-- delete duplicates, keep lowest Id
WITH d AS (
SELECT Id, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) AS rn
FROM Users
)
DELETE FROM d WHERE rn > 1;
After cleanup, add a unique index on Email to stop duplicates returning.
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates like COUNT or SUM. For example, WHERE City = 'Pune' keeps only Pune rows, then GROUP BY DepartmentId HAVING COUNT(*) > 10 keeps departments with more than 10 such employees. Put non-aggregate conditions in WHERE so fewer rows are grouped, which is faster.
06
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows one by one, supports WHERE, fires triggers and is fully logged, so it is slower on big tables. TRUNCATE removes all rows by deallocating pages, is minimally logged, resets identity values and cannot have WHERE; it fails if foreign keys reference the table. DROP removes the whole table including structure, indexes and permissions. In SQL Server, both DELETE and TRUNCATE can be rolled back inside a transaction.
07
Explain normalisation and the first three normal forms.
Normalisation organises tables to reduce redundancy and update anomalies. 1NF: each column holds atomic values and there are no repeating groups. 2NF: in 1NF and every non-key column depends on the whole primary key, not part of a composite key. 3NF: in 2NF and non-key columns do not depend on other non-key columns, for example City should not be stored with an order if it depends on the customer. Reporting databases may denormalise for speed.
08
What is SQL injection and how do you prevent it in a .NET application?
SQL injection happens when user input is concatenated into SQL text, letting attackers change the query, for example entering ' OR 1=1 -- in a login box. Prevent it by always using parameters: SqlCommand with SqlParameter, EF Core LINQ, FromSql with interpolation, or Dapper parameters. Also use least-privilege database accounts, validate input and never show raw database errors to users. Stored procedures help only if they also avoid dynamic SQL concatenation.
09
What are your strengths and weaknesses as a developer?
My strength is that I go deep when debugging; I like reading execution plans and logs until I find the real cause, which has helped my team fix slow queries. A weakness was that I spent too long polishing code before asking for feedback. I now open small draft pull requests early and ask for review, which has made me faster and helped me learn from seniors sooner.
In five years I want to be a senior full stack engineer who can design a complete .NET system, from database and APIs to front end and cloud deployment, and guide newer developers. In the near term I want to master ASP.NET Core, EF Core and SQL Server performance, then grow into Azure and system design. I would like to grow inside one company where I can take on bigger modules over time.
11
What is the difference between a clustered and a non-clustered index?
A clustered index defines the physical order of the table's data, so the leaf level is the data itself, and a table can have only one. A non-clustered index is a separate structure with key values and a pointer back to the row, and a table can have many. Range queries on the clustered key are fast. Non-clustered indexes speed up searches on other columns but add cost to inserts and updates.
12
What is the difference between a stored procedure and a user-defined function?
A stored procedure can perform inserts, updates and deletes, use transactions and TRY-CATCH, return multiple result sets and output parameters, and is called with EXEC. A function must return a value or table, cannot modify database state, and can be used inside SELECT, WHERE and JOIN. Scalar functions used row by row can hurt performance, although SQL Server 2019 and later can inline many of them.
13
Show the difference between ROW_NUMBER, RANK and DENSE_RANK with a query.
SELECT Name, Department, Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rnk,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRnk
FROM Employees;
-- salaries 90, 90, 80 give
-- ROW_NUMBER: 1, 2, 3
-- RANK: 1, 1, 3
-- DENSE_RANK: 1, 1, 2
PARTITION BY restarts numbering for each department.
14
A query that used to run in 1 second now takes 30 seconds. How do you troubleshoot it?
Capture the actual execution plan and look for table or index scans on large tables, key lookups, big differences between estimated and actual rows, and warnings like implicit conversions or spills to tempdb. Check missing or fragmented indexes and outdated statistics, and update them. Use Query Store to compare the old and new plans; if a plan regressed, you can force the good plan while fixing the root cause.
15
What is the difference between temporary tables and table variables?
Temporary tables (#Temp) live in tempdb, support indexes and statistics, and are visible to nested procedures, so they suit larger intermediate results. Table variables (@T) also use tempdb but have limited statistics, which made old optimisers estimate one row; SQL Server 2019 improved this with deferred compilation. Table variables are not affected by transaction rollback. Use table variables for small sets and temp tables for large ones.
16
Write a stored procedure that transfers money between accounts with TRY-CATCH and a transaction.
CREATE OR ALTER PROCEDURE usp_Transfer @From INT, @To INT, @Amt DECIMAL(18,2)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - @Amt
WHERE Id = @From AND Balance >= @Amt;
IF @@ROWCOUNT = 0 THROW 50001, 'Insufficient balance', 1;
UPDATE Accounts SET Balance = Balance + @Amt WHERE Id = @To;
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
END
A view is a saved SELECT query that acts like a virtual table; it simplifies complex joins and can restrict which columns users see, but by default it stores no data. An indexed view has a unique clustered index, so SQL Server physically stores and maintains its results, which speeds up heavy aggregations. The cost is slower writes on base tables, and it requires SCHEMABINDING and deterministic expressions.
18
Tell me about a time you made a mistake that affected data, and what you did.
Early in my job (Situation) I ran an UPDATE on a test copy that I believed was staging, but it was a shared QA database, and 2,000 test orders lost their status. My task was to fix it fast. I told my lead immediately, restored the rows from the nightly backup with the DBA, and verified counts. Result: QA lost only an hour. Since then I always wrap manual updates in a transaction and run a SELECT with the same WHERE first.
19
Tell me about a time you worked under a tight deadline.
During my internship (Situation) a client demo was moved two days earlier, and the reports page was not ready. My task was to deliver the SQL queries and API for it. I listed must-have reports with my mentor, wrote and tested the three critical queries first, and postponed exports. I stayed in touch with the UI developer every few hours. Result: the demo went well, and we added the remaining features the next week.
20
Write a recursive CTE that lists an employee hierarchy with levels.
WITH Org AS (
SELECT Id, Name, ManagerId, 0 AS Lvl
FROM Employees
WHERE ManagerId IS NULL -- anchor: CEO
UNION ALL
SELECT e.Id, e.Name, e.ManagerId, o.Lvl + 1
FROM Employees e
JOIN Org o ON e.ManagerId = o.Id -- recursive part
)
SELECT REPLICATE(' ', Lvl) + Name AS Hierarchy, Lvl
FROM Org
OPTION (MAXRECURSION 100);
The anchor query runs once and the recursive part repeats until no new rows are found.
21
What are ACID properties and the transaction isolation levels in SQL Server?
ACID means Atomicity (all or nothing), Consistency (rules hold), Isolation (transactions do not interfere) and Durability (committed data survives crashes). Isolation levels are READ UNCOMMITTED (dirty reads allowed), READ COMMITTED (the default), REPEATABLE READ, SERIALIZABLE (strictest) and SNAPSHOT, which uses row versions so readers do not block writers. Read Committed Snapshot Isolation is often enabled to reduce blocking in busy OLTP apps.
22
Users report intermittent errors saying their transaction was chosen as a deadlock victim. How do you investigate and fix it?
A deadlock happens when two sessions each hold a lock the other needs. Get the deadlock graph from the system_health Extended Events session to see the statements and objects involved. Common fixes: access tables in the same order in all code paths, keep transactions short, add indexes so queries lock fewer rows, and consider RCSI. Also add retry logic in the app, since EF Core's SQL Server execution strategy can retry deadlocks.
23
A frequent query on Orders filtered by CustomerId shows expensive key lookups in its execution plan. How would you fix it?
A covering index contains every column a query needs, so SQL Server answers it from the index alone without key lookups to the clustered index. INCLUDE adds non-key columns to the leaf level only, keeping the index key small. For SELECT Name, Total FROM Orders WHERE CustomerId = 5, an index on CustomerId INCLUDE (Name, Total) covers it. Do not include too many columns, since every index slows writes.
24
A stored procedure is fast for most customers but very slow for one large customer, and sometimes slow for everyone after a restart. What could cause this?
This is parameter sniffing. SQL Server compiles a plan using the first parameter values it sees and reuses it. A plan built for a small customer, using index seeks, can be terrible for a huge one, and vice versa. Fixes include OPTION (RECOMPILE) for that statement, OPTIMIZE FOR, splitting logic for skewed values, or Query Store plan forcing. SQL Server 2022 adds Parameter Sensitive Plan optimisation to keep multiple plans.
25
An Orders table has 50 million rows and queries are slowing down, but most reports only need the last 12 months. What would you do?
First check indexes and query plans, since good indexing often solves it. Then archive older data: move rows older than a set period to an archive table or database in small batches during off-peak hours, keeping reports on history available separately. For ongoing growth, use table partitioning by month on OrderDate, which enables fast partition switching and lets queries scan only relevant partitions. Agree retention rules with the business first.
Frequently asked questions
How important is SQL Server and T-SQL in .NET Full Stack interviews?
Relational database skills every .NET developer needs: keys, joins, indexes, normalisation, stored procedures, functions, CTEs, window functions, transactions and isolation levels. You will practise common query problems and learn to read execution plans, handle deadlocks and parameter sniffing, and manage large tables that power business applications.
How should I use the SQL Server and T-SQL sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.