Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 39 ยท Projects and Practice

SQL Server Interview Questions and Answers

Prepare for SQL Server interviews with clear answers on T-SQL, joins, indexes, transactions, locking, performance tuning, backups and security, plus coding questions you can run.

SQL Server interviews usually mix three things: can you write T-SQL, do you understand how the engine behaves, and can you keep a database healthy. This lesson collects the questions that come up most, with short answers that show understanding rather than memorized definitions. The better answers include a reason, an example and a trade-off.

Use it as a revision list after the course: each answer points to the lesson that explains it. Some coding questions are runnable.

T-SQL fundamentals

What is the difference between DELETE, TRUNCATE and DROP? DELETE removes rows one at a time (logged, fires triggers, accepts WHERE). TRUNCATE deallocates pages: fast, resets identity, no WHERE, no triggers, and not allowed when foreign keys reference the table. DROP removes the whole object. See INSERT, UPDATE and DELETE.

WHERE versus HAVING? WHERE filters rows before grouping; HAVING filters groups after aggregation, so it can use aggregates.

What is the difference between UNION and UNION ALL? UNION removes duplicates (it sorts or hashes to do so); UNION ALL keeps everything and is faster. Use UNION ALL when duplicates are impossible or acceptable.

How do RANK, DENSE_RANK and ROW_NUMBER differ? Run it:

SQL Ranking functions side by side

Press Run (or Ctrl + Enter) to see the result here.

ROW_NUMBER is always unique; RANK leaves gaps after ties (1, 2, 2, 4); DENSE_RANK doesn't (1, 2, 2, 3).

How do you find the second highest salary? Several ways; the window approach generalizes to the Nth:

SQL Nth highest value

Press Run (or Ctrl + Enter) to see the result here.

In T-SQL you can also write SELECT DISTINCT salary FROM dbo.Emp ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY.

How do you remove duplicate rows? Number them within each duplicate group and delete numbers above 1. In T-SQL you can delete straight through a CTE: WITH d AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) AS rn FROM dbo.Users) DELETE FROM d WHERE rn > 1;

SQL Find duplicates to delete

Press Run (or Ctrl + Enter) to see the result here.

What is the difference between a CTE, a temp table and a table variable? A CTE is a named query for one statement (not stored). A #temp table is real, indexable and has statistics. A table variable is scoped to the batch, isn't rolled back, and has limited statistics. See variables and control flow.

NULL handling. NULL = NULL is unknown, not true: use IS NULL. COUNT(col) ignores NULLs, COUNT(*) doesn't. ISNULL(a, b) is T-SQL-specific; COALESCE is standard and takes many arguments.

Programmability

Stored procedure versus function? Procedures can change data, return multiple result sets, use transactions and TRY...CATCH; functions can be used inside queries but can't modify data. Inline table-valued functions perform best; scalar UDFs can be slow. See procedures and functions.

What are the inserted and deleted tables? Virtual tables in a trigger holding new and old row versions. Triggers fire once per statement, so code must handle many rows. See triggers.

How do you prevent SQL injection? Parameterize everything (sp_executesql with parameters, never string concatenation), whitelist and QUOTENAME identifiers, use least-privilege accounts. See dynamic SQL.

@@IDENTITY, SCOPE_IDENTITY() or IDENT_CURRENT()? SCOPE_IDENTITY() returns the last identity in your session and scope: the safe choice. @@IDENTITY includes triggers' inserts; IDENT_CURRENT is table-wide and session-independent.

Transactions and concurrency

Explain ACID. Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions don't see each other's partial work) and Durability (committed data survives a crash, thanks to the log). See transactions.

What are the isolation levels and the default? READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE and SNAPSHOT. READ_COMMITTED_SNAPSHOT makes readers use row versions instead of locks. See isolation and locking.

What is a deadlock and how do you avoid it? Two sessions each hold a lock the other needs; SQL Server kills one (error 1205). Avoid with consistent object order, short transactions, good indexes and row versioning; retry in the application.

Why is NOLOCK risky? It allows dirty reads, and can skip or double-read rows during page splits. Prefer snapshot isolation.

Indexes and performance

Clustered versus nonclustered index? The clustered index is the table, sorted by its key (one per table). Nonclustered indexes are separate structures pointing back to the rows. See indexes.

What is a covering index? A nonclustered index containing every column a query needs (key plus INCLUDE), so there are no key lookups. See covering indexes.

A query is slow. What do you do? Reproduce it, get the actual plan and STATISTICS IO; compare estimated and actual rows; look for scans, lookups, spills, implicit conversions and non-SARGable predicates; fix one thing; measure again. Check Query Store for regressions. See execution plans and Query Store.

What is parameter sniffing? The plan is compiled for the first parameter values and reused, which can be bad for others. Mitigate with OPTION (RECOMPILE), OPTIMIZE FOR, better indexes or Query Store hints.

Index scan or seek, which is better? Neither always: a seek is best for few rows, a scan for most of the table. Judge by logical reads.

Administration and security

Recovery models? SIMPLE (no log backups, restore to last full/differential), FULL (point-in-time with log backups), BULK_LOGGED. What if the log keeps growing? Often FULL recovery without log backups, or an open transaction. See backup and restore.

Which backup chain restores to 14:07? The last full, the latest differential, then every log backup up to the one covering 14:07, using STOPAT, with NORECOVERY until the last step.

Login versus user? A login connects to the server; a user is its identity inside one database. Orphaned users arise after restores. See permissions.

TDE versus Always Encrypted? TDE encrypts files at rest transparently; Always Encrypted keeps keys at the client so even DBAs can't read the values. See security features.

What does SQL Server Agent do? Schedules jobs, raises alerts and notifies operators; definitions live in msdb. See Agent and maintenance.

Scenario questions

  • "Users report the app is slow at 9 am." Check waits, blocking and Query Store for that window before touching anything.
  • "Someone ran DELETE without WHERE an hour ago." Take a tail-log backup, restore the full, differential and logs to a new database up to just before the mistake, and copy the rows back.
  • "The application account needs to read orders but not salaries." Put the tables in different schemas, grant SELECT on the first only (or use column permissions and views), never db_datareader.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function should you use to read the identity value just inserted in your own scope?

    SCOPE_IDENTITY() ignores identities created by triggers and other sessions.
  2. 2. Ties at the top score: which ranking gives the sequence 1, 2, 2, 3?

    DENSE_RANK does not leave gaps after ties; RANK would give 1, 2, 2, 4.
  3. 3. What is the first thing to do for a slow query?

    Evidence first, plans, estimates versus actuals and reads show the real cause.
  4. 4. Which restore step keeps the database ready for further backups?

    NORECOVERY leaves the database in the restoring state so differentials and logs can follow.

Summary

  • Interviewers want reasons and trade-offs: say why, give an example, name the downside.
  • Know the T-SQL basics cold: joins, windows, CTEs, NULLs, ranking.
  • Be able to explain transactions, isolation, indexes, plans and the backup chain in plain words.
  • For scenarios, describe a method: gather evidence, change one thing, verify.

Next: Back to the SQL Server course