Beware of nondeterministic T-SQL code
summary
Shows concrete failures and fixes: pagination with OFFSET ... FETCH or TOP returns nonrepeatable rows when ORDER BY isn’t a strict total order, because the optimizer chooses different plans; solution is to add a tiebreaker (e.g., OrderID) or use TOP ... WITH TIES semantics. Demonstrates using a CTE to separate filter ordering from presentation ordering, explains when to use ROW_NUMBER vs RANK/DENSE_RANK, and how a running total needs RANGE (not ROWS) to emulate WITH TIES. Ends with a constant-folding trick to force a non-sorting plan.
AI-written summary · about
chapters (10)
- 0:00Introduction to the session
- 1:14Understanding determinism
- 4:21Pagination and ordering issues
- 6:47Offset fetch design
- 9:30Fixing non-deterministic pagination
- 16:31Handling ties in top filters
- 21:23Separating filtering and presentation
- 27:02Window functions and ranking
- 35:12Running totals and scope
- 45:46Advanced non-deterministic tricks
related talks
51mBeware of nondeterministic T-SQL code
54mBecoming a SQL Guru
41mRow-Estimation Revelation and the Monolithic Query Syndrome
46mT-SQL Window Function Performance
51mHere’s the execution plan … now what?
53mBanishing Slow Queries with Intelligent Query Processing
49mModern T SQL for Better Performance
53mWhen to stop tuning a query