Aaron Bertrand has a clever trick:
In the most basic cases, we can implement this functionality in SQL Server by using
OFFSET/FETCH. The problem is that anything that usesTOPorOFFSETwill potentially have to scan everything in the index up until the page requested, which means that queries become slower and slower for higher page numbers. To achieve anything close to linear performance, you need to have a narrow, covering index for each sort option, or use columnstore as Erik Darling recommends here, or concede that some searches are just going to be slow. Throw in additional filtering, pulling data from other tables, and letting users dictate any sort order they want, and it becomes that much harder to tune for all cases.I have a lot that I want to say about paging, and I will follow up with more content soon. Several years ago, I wrote about some ways to reduce the pain here, and it is long overdue for a refresh. For today’s post, though, I wanted to talk specifically about pagination when you have to order by large values. By “large” I mean any data type that can’t fit in an index key, like
nvarchar(4000)or, really, anything that can’t lead in an index and/or would push the key past 1,700 bytes.
Read on for the scenario and how it all works.