The Impact of Sorting and Filters on Pagination

Aaron Bertrand continues digging into SQL Server pagination performance:

In my previous tip, Pagination Performance in SQL Server, I showed how to make SQL pagination more predictable – turning O(n) into O(1). I materialized and cached row numbers to page through instead of calculating them on every request. It wasn’t the whole story, though; real pagination queries rarely get to sort without filtering. Users always want more control, and filtering can threaten that predictability.

Read on for examples of how to handle a few different scenarios.