Some Tricky SQL Server questions that often trip up developers

 

1. NULL Handling

Question:

What is the result of SELECT NULL = NULL in SQL Server?
Answer:
It returns NULL, not TRUE or FALSE. Use IS NULL instead:

sql

SELECT CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END; -- Returns 'TRUE'

---

2. Implicit Conversion Pitfalls

Question:
Why does SELECT 5 + '5' return 10, but SELECT '5' + 5 throws an error?
Answer:
SQL Server implicitly converts the string '5' to an integer in the first case but fails in the second because '5' is treated as a string first. Use explicit casting:

sql

SELECT CAST('5' AS INT) + 5; -- Returns 10

---

3. The "Halloween Problem"

Question:
What happens if you run this query?

sql

UPDATE Employees SET Salary = Salary * 1.1 WHERE Salary < 5000;

Answer:
If an employee’s updated salary crosses the 5000 threshold during the update, they might be updated multiple times (due to how the query plan scans the data). Use WITH (TABLOCK) or split the query into batches.

---

4. JOINs vs. WHERE Clauses

Question:
What’s the difference between these two queries?

sql

-- Query 1
SELECT * FROM TableA LEFT JOIN TableB ON A.ID = B.ID WHERE B.ID IS NOT NULL;

-- Query 2
SELECT * FROM TableA INNER JOIN TableB ON A.ID = B.ID;

Answer:
Both return the same result, but Query 1 is less efficient because it performs a LEFT JOIN and then filters out NULLs. Query 2 uses an INNER JOIN directly.

---

5. The "Gaps and Islands" Problem

Question:
How do you find gaps in a sequence of numbers (e.g., missing order IDs)?
Tricky Answer:
Use LEAD() or LAG():

sql

WITH CTE AS (
  SELECT OrderID, NextOrderID = LEAD(OrderID) OVER (ORDER BY OrderID)
  FROM Orders
)
SELECT GapStart = OrderID + 1, GapEnd = NextOrderID - 1
FROM CTE
WHERE NextOrderID - OrderID > 1;

---

6. Deadlocks and Locking

Question:
Why does this query cause a deadlock?

sql

-- Session 1
BEGIN TRAN;
UPDATE TableA SET Col1 = 1 WHERE ID = 1;
UPDATE TableB SET Col2 = 2 WHERE ID = 1;

-- Session 2 (run concurrently)
BEGIN TRAN;
UPDATE TableB SET Col2 = 2 WHERE ID = 1;
UPDATE TableA SET Col1 = 1 WHERE ID = 1;

Answer:
Each transaction holds a lock on one table and waits for the other. Use consistent update order or SET DEADLOCK_PRIORITY.

---

7. CTE Recursion Limits

Question:
What happens if you run this recursive CTE?

sql

WITH CTE AS (
  SELECT 1 AS Number
  UNION ALL
  SELECT Number + 1 FROM CTE WHERE Number < 1000
)
SELECT * FROM CTE OPTION (MAXRECURSION 0);

Answer:
By default, SQL Server limits recursion to 100 levels. MAXRECURSION 0 removes the limit but risks infinite loops.

---

8. MERGE Statement Gotchas

Question:
Can the MERGE statement cause primary key violations?
Answer:
Yes! If multiple source rows match the same target row, MERGE can attempt duplicate inserts. Always deduplicate source data first.

---

9. String Splitting

Question:
What’s wrong with using STRING_SPLIT in SQL Server 2016+?
Answer:
STRING_SPLIT doesn’t guarantee the order of output rows. Use OPENJSON or a custom splitter for ordered results.

---

10. The "COUNT(*) vs. COUNT(Column)" Trap

Question:
What’s the difference between COUNT(*) and COUNT(Column)?
Answer:

• COUNT(*) counts all rows.

• COUNT(Column) counts non-NULL values in that column.

---

11. The "NOLOCK" Hint Myth

Question:
Does WITH (NOLOCK) make queries faster?
Answer:
Yes, but it allows dirty reads (uncommitted data) and is not a substitute for proper isolation levels. Use READ COMMITTED SNAPSHOT instead.

---

12. Paging with OFFSET-FETCH

Question:
Why does OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY perform poorly?
Answer:
It scans all previous rows (1000+10). Use keyset pagination instead:

sql

SELECT * FROM Orders WHERE OrderID > @LastSeenID ORDER BY OrderID FETCH NEXT 10 ROWS ONLY;

---

13. The "UPDATE with JOIN" Surprise

Question:
What does this query do?

sql

UPDATE A
SET A.Col1 = B.Col2
FROM TableA A
INNER JOIN TableB B ON A.ID = B.ID;

Answer:
It updates TableA using values from TableB where IDs match. The syntax is valid but often confuses developers expecting a WHERE clause.

---

14. The "DISTINCT" Misconception

Question:
Does SELECT DISTINCT remove duplicates from the entire row?
Answer:
Yes, but it’s resource-heavy. Use GROUP BY with aggregates or window functions for better control.

---

15. The "DATETIME vs. DATETIME2" Quirk

Question:
Why does WHERE DateColumn = '2023-10-01' fail for DATETIME?
Answer:
DATETIME has a time component (3.33ms precision). Use CAST to DATE:

sql

WHERE CAST(DateColumn AS DATE) = '2023-10-01';

---

Bonus: The "Ghost Records" Issue

Question:
Why does a table’s size not reduce after deleting rows?
Answer:
SQL Server marks rows as "ghost records" for potential rollbacks. Rebuild the index or use ALTER TABLE ... REBUILD.

---

How to Prepare for Tricky SQL Questions

1. Understand Execution Plans: Use SET SHOWPLAN_TEXT ON to analyze query logic.

2. Test Edge Cases: Try NULLs, duplicates, and empty datasets.

3. Master Window Functions: ROW_NUMBER(), RANK(), and LEAD()/LAG().

4. Learn Isolation Levels: READ UNCOMMITTED, SNAPSHOT, etc.