Eleven SQL Server Interview Questions That Look Far Too Easy
This title could be clearer and more informative.Try out Clickbait Shieldfor free (5 uses left this month).
Eleven SQL Server behaviors that trip up even senior engineers, tested and verified on SQL Server 2025 RTM-CU7. Covers ANSI padding making 'SQLAuthority' equal 'SQLAuthority ' despite LEN vs DATALENGTH disagreeing, why trailing-space detection differs for varchar vs char, SUM returning NULL versus COUNT returning 0 on empty tables, NULL comparisons silently excluding rows in WHERE clauses, integer division truncation, column alias scoping rules across WHERE and ORDER BY, IDENTITY values not being restored after ROLLBACK, deleting duplicate rows with no key, disabling a clustered index making a table completely inaccessible, an added index actually making a SELECT slower due to optimizer cost decisions, and foreign keys referencing unique (non-primary-key) columns plus silently allowed duplicate indexes. Each example includes runnable T-SQL and actual output, framed as interview questions meant to reveal real understanding rather than memorized trivia.
Table of contents
1. Is ‘SQLAuthority’ the same as ‘SQLAuthority ‘?2. How would you find every row that has a trailing space?3. A table has no rows at all. What do SUM and COUNT return?4. Does WHERE Status <> ‘Active’ return the rows where Status is NULL?5. What does SELECT 1/2 return?6. Why can you use a column alias in ORDER BY but not in WHERE?7. You insert a row, then roll back. What is the next identity value?8. Two rows are completely identical. No key, no identity. Delete exactly one.9. What happens to a table if you disable its clustered index?10. Can adding an index make a SELECT slower?11. Can a foreign key reference a column that is not the primary key?How I Would Actually Use TheseQuestions this post answers
Why does SQL Server say 'SQLAuthority' equals 'SQLAuthority ' with a trailing space?
SQL Server follows ANSI blank-padding rules, so the shorter string is padded with spaces before an equality comparison, making the two values equal regardless of collation (case-sensitive, accent-sensitive, or binary all treat them as equal). This only affects trailing spaces and only equality comparisons; LEN ignores trailing spaces (returns 12) while DATALENGTH counts them (returns 13), and LIKE does not follow the same padding rule and behaves asymmetrically. daily.dev surfaces practical SQL Server gotchas like this for developers debugging unexpected query results.
Does disabling a clustered index in SQL Server just slow down queries or does it break the table completely?
Disabling a clustered index makes the entire table inaccessible immediately, not just slower, because the clustered index physically is the table. Both SELECT and INSERT statements fail with error 8655 stating the query processor cannot produce a plan. Recovery requires an ALTER INDEX ALL ... REBUILD statement, since there is no ENABLE command for a disabled clustered index. developers investigating index-related outages can track these SQL Server internals through daily.dev.
Can adding a nonclustered index make a SELECT query slower in SQL Server?
Yes, forcing a query to use an index instead of letting the optimizer choose can more than double logical reads. In one test on a 500,000-row table where a filter matched about 2% of rows, an unindexed scan took 13,942 logical reads, adding an index without hints kept the optimizer at 13,942 reads (it chose to ignore the new index), but forcing the index with an INDEX hint pushed reads to 30,796 because seeking plus key lookups cost more than a full scan. engineers weighing index hints versus optimizer choices can follow SQL Server performance deep dives on daily.dev.