5 to 7 years experience - Sample advanced SQL Server questions

​

Advanced SQL Server Interview Questions and Answers

• Explain the difference between a correlated subquery and a nested subquery.
  A correlated subquery refers to columns from the outer query and is evaluated repeatedly for each row of the outer query. A nested (or non-correlated) subquery is independent of the outer query and runs once.

  ​

• Write a query to find the top 5 customers with the highest total order amounts.

  sql

  SELECT TOP 5 CustomerID, SUM(OrderAmount) AS TotalOrderAmount FROM Orders GROUP BY CustomerID ORDER BY TotalOrderAmount DESC;

  This aggregates order amounts per customer and returns the top 5.

  ​

• How would you handle NULL values in SQL Server queries?

  
  Use functions like ISNULL(), COALESCE(), or CASE expressions to replace or handle NULLs appropriately depending on the use case.

  ​

• Explain database partitioning and its benefits.
  Partitioning breaks a large table into smaller, manageable segments to improve query performance by limiting the search scope and aiding maintenance and scalability.

  ​

• Describe the functionality of MERGE statements in SQL Server.
  The MERGE statement allows combining INSERT, UPDATE, and DELETE operations into a single atomic statement based on specified conditions, improving efficiency in data synchronization tasks.

  ​

• Write a query to find the difference in days between order and ship dates.

  sql

  SELECT OrderID, DATEDIFF(day, OrderDate, ShipDate) AS DaysDifference FROM Orders;

  This uses the DATEDIFF function to compute the day difference.

  ​

• How do you find employees who have never placed an order?

  sql

  SELECT e.EmployeeID, e.EmployeeName FROM Employees e LEFT JOIN Orders o ON e.EmployeeID = o.EmployeeID WHERE o.OrderID IS NULL;

  A LEFT JOIN with NULL check on the order side finds employees without orders.

  ​

• Write a query to get the nth highest salary in an employee table.
  A common approach uses a subquery with ROW_NUMBER() or nested MAX filters to retrieve the nth highest salary.

  ​

• Explain common types of indexes in SQL Server and their usage.
  Clustered indexes define physical order of data, and non-clustered indexes improve search speed without altering physical storage. Indexes significantly affect query performance.

  ​

• How do you approach query optimization for slow-running queries?
  Analyze execution plans, reduce scans with indexes, avoid cursors, use appropriate joins, and rewrite queries to be set-based.

  ​

Interview Preparation Tips

• Be ready to write queries and optimize them on the spot.

• Explain your thought process for troubleshooting and real-case problem solving.

• Understand performance tuning techniques, indexing, backup strategies, and replication basics.

• Practice recursive CTEs, window functions, and complex joins as they are frequent topics.

This selection covers core advanced concepts and practical SQL Server scenarios you can expect for 5-7 years experienced roles.