Aleksandar Cebov (4/26/2010)
Hmmm... it seems to me that this will work only if where statement is not being used.
It works whenever there is a useful index to obtain the keys, in the required order.
What if we need to do server side paging and sorting and apply filter on which rows should be returned (maybe in the example I need to get only those records where the thread_id=some_value and sort them by create_dt)?
The appropriate Key-Seek index in that case would be on (thread_id, create_dt). There is a full reproduction script included with the article. I encourage you to download it, and experiment for yourself.
Custom sorting and filtering is covered in more depth in part III - this first part is just about establishing the basic method.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi