SQL Server Interview Questions

INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all rows from the left table, with NULLs for unmatched right-hand columns.

WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can use aggregate functions.

1SELECT Department, COUNT(*) AS Staff
2FROM Employees
3WHERE IsActive = 1
4GROUP BY Department
5HAVING COUNT(*) > 5;

DELETE removes rows (optionally filtered), is fully logged and fires triggers. TRUNCATE removes all rows quickly, resets identity and cannot use WHERE. DROP removes the table itself.

A primary key uniquely identifies each row and cannot be NULL (one per table). A unique constraint enforces uniqueness but allows one NULL. A foreign key references a primary/unique key in another table to enforce referential integrity.

A database that stores data in tables of rows and columns, links tables through keys and is queried with SQL. Constraints and transactions keep the data consistent. SQL Server, MySQL and PostgreSQL are examples.

Pick the smallest type that fits the domain: int or bigint for ids, decimal(18,2) for money (never float), nvarchar(n) with a sensible length for text, date or datetime2 for dates and bit for flags. Good types save space and make indexes faster.

FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally TOP/OFFSET. That is why a column alias from SELECT can be used in ORDER BY but not in WHERE.

ORDER BY one or more columns (ASC/DESC), then OFFSET n ROWS FETCH NEXT m ROWS ONLY. Without ORDER BY the order is not guaranteed, so paging needs a deterministic order, often ending with the primary key.

COUNT(column), SUM, AVG, MIN and MAX ignore NULLs; COUNT(*) counts rows. AVG over values with NULLs therefore divides by the non-null count, which can surprise you.

A clustered index defines the physical order of the table’s rows; there can be only one. A non-clustered index is a separate structure with keys and pointers to rows; a table can have many.

Parameterised procedure calls do, because values are never parsed as SQL. A procedure that builds dynamic SQL by concatenating parameters is still vulnerable; use sp_executesql with parameters.

A Common Table Expression (WITH name AS (…)) is a named, temporary result set for one statement. It improves readability, can be referenced multiple times and supports recursion for hierarchies.

All are window functions. ROW_NUMBER gives unique sequential numbers. RANK gives ties the same rank and leaves gaps (1,1,3). DENSE_RANK gives ties the same rank without gaps (1,1,2).

1NF: atomic values, no repeating groups. 2NF: 1NF and every non-key column depends on the whole key. 3NF: 2NF and no non-key column depends on another non-key column. It reduces redundancy and update anomalies.

A view is a saved SELECT used like a table. A stored procedure runs any logic, can modify data and return multiple result sets. A function returns a value or table, can be used in queries, but cannot change data.

Always include a WHERE clause, test it first as a SELECT, wrap risky changes in a transaction, check @@ROWCOUNT, and use OUTPUT to see what changed. In production, take a backup or use a script reviewed by someone else.

1BEGIN TRANSACTION;
2UPDATE dbo.Products
3SET Price = Price * 1.10
4OUTPUT deleted.Price AS OldPrice, inserted.Price AS NewPrice
5WHERE CategoryId = 3;
6-- check the result, then
7COMMIT;

Use a JOIN when you need columns from both tables. Use EXISTS or IN subqueries to filter by the existence of related rows. The optimiser often produces the same plan; choose the one that expresses the intent clearly.

A scalar or table-valued function written in T-SQL. Inline table-valued functions are expanded like views and perform well. Multi-statement and older scalar functions run row by row and can hide expensive work.

Atomicity: all statements commit or all roll back. Use BEGIN TRAN, COMMIT and ROLLBACK with TRY…CATCH and SET XACT_ABORT ON so any error rolls back cleanly. Keep transactions short to avoid blocking.

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK (Price >= 0), DEFAULT and NOT NULL. Constraints are enforced for every application that writes to the table, unlike validation in one app.

A natural key comes from the data (email, ISBN); a surrogate key is generated (INT IDENTITY or GUID). Surrogates are stable and compact, so they usually make the primary key, with a UNIQUE constraint on the natural key.

READ UNCOMMITTED allows dirty reads; READ COMMITTED (default) prevents them; REPEATABLE READ prevents non-repeatable reads; SERIALIZABLE prevents phantoms; SNAPSHOT uses row versioning so readers don’t block writers.

Read the actual execution plan, look for scans on large tables, key lookups, implicit conversions and bad estimates; add or adjust indexes, make predicates SARGable, update statistics and avoid functions on indexed columns in WHERE.

They run hidden logic on every insert/update/delete, can cascade, make debugging harder and hurt bulk performance. Prefer explicit logic in procedures or the application unless auditing truly requires triggers.

Read right to left. Look for scans where seeks are expected, key lookups, large differences between estimated and actual rows, expensive sorts, spills to tempdb and warnings. Compare logical reads with SET STATISTICS IO ON.

SQL Server compiles a plan using the first parameter values it sees and reuses it. If those values were unusual, the cached plan can be terrible for others. Fixes include OPTION (RECOMPILE), OPTIMIZE FOR, better indexes or Query Store plan forcing.

Give the app a dedicated login with least privilege (often only EXECUTE on procedures or specific schemas), never use sa, parameterise all queries, encrypt connections, use Always Encrypted or TDE for sensitive data and audit access.

ROWS or RANGE BETWEEN … defines which rows a window aggregate sees. SUM(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is a running total; ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a 7-row moving sum.