Paul White NZ (9/4/2010)
Hugo Kornelis (9/4/2010)
My examples on page 11 in this topic still produce incorrect results.If we add the safety check, things work out nicely:
(...)
Correct results and an optimal query plan! 🙂
Indeed. The check itself changes the options for the optimizer. The method now even appears to be immune to parallel execution problems, since the parallel streams have to be gathered and re-synched before the row numbers can be calculated.
There may be a way to break this method on current versions of SQL Server, but it takes someone smarter then me to find it. And even if you accidentally stumble over it, or if new versions of the optimizer start to wreck this method, you're still safe because of the builtin safety check.