Ioannis Tsakpinis (5/24/2010)
Hey Paul and thanks for this series, both articles were great.
Thanks, I'm glad you enjoyed them. There will be at least one more in this series.
I think the double-sort situation in the Double Row Number method can be avoided if you simply swap the ROW_NUMBER() OVER statements. That is, calculate the descending order first and then the ascending (or the opposite if you want last-to-first paging). I tried this on a paging query in our application (SQL Server 2005) and it works great, one of the two sorts is gone.
This is true, and something that I missed when writing the test rig. Nonetheless, even one full sort of the full million-row set is enough to make the double-row-number method a poor choice in my view. Thanks for pointing this out though!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi