Beware of nondeterministic T-SQL code
summary
Shows concrete nondeterminism bugs and fixes: pagination with OFFSET/FETCH returns duplicate rows unless ordering is a strict total order (add a unique tiebreaker like orderID), TOP can use different indexes so include the tiebreaker or use TOP ... WITH TIES carefully, ROW_NUMBER vs RANK/DENSE_RANK choices change results, CTEs inline and can be evaluated twice causing different row numbers, running totals need RANGE or grouping+window sum, and a trick to avoid sorting uses a non‑foldable constant expression.
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
46mT-SQL Window Function Performance
41mRow-Estimation Revelation and the Monolithic Query Syndrome
49mModern T SQL for Better Performance
54mBecoming a SQL Guru
53mWhen to stop tuning a query- 53mWhen to stop tuning a query
46mAwesome SQL – tips and tricks for application developers