Anthony Perkins (5/24/2010)
Thank you for the very good article.
Thanks for reading it 🙂
One question I have, has to do with the need for an ORDER BY clause for the overall SQL statement in the first CTE (Keys). Yes, I understand that using a TOP without an ORDER BY does not guarantee results, however the combination of the ORDER BY in the Window function and the WHERE Clause should take care of that. Is there something I am missing? Does it even matter?
Yes, it matters.
The Keys CTE is all about finding the TOP (@PageNumber * @PageSize) rows in (observation_date, observation_hour, observation_minute) order. If we want page 5, where each page has 10 rows, the Keys CTE will return just 5 * 10 = 50 rows (in that defined order) for the next CTE to work on (logically speaking).
The ORDER BY in the ranking function only applies to the ranking function, so we cannot assume it affects the TOP operation in any way. The WHERE clause is just there to specify the overall restriction on the date range we are interested in, so that does not help either.
For more information the technical details of TOP with ORDER BY, see the following post by Connor Cunningham:
http://blogs.msdn.com/queryoptteam/archive/2006/05/02/588731.aspx
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi