Pagination (or paging) is the process used to return a set of results in pages. When it comes to pagination in SQL Server, you have a couple of different options. Today, we’ll look at three of them. The first being pagination with the use of FETCH/OFFSET, the second using a CTE, and the third with the use of ROW_NUMBER. Let’s look at a brief example of how each works.
For each example, I’m using @PageSize to determine how many results to return and @PageNumber to determine what page of results to return from the StackOverflow2013 database.
Chad has three techniques listed here. Pagination is one of those things which should be simple but all of the simple options have major performance drawbacks once you get to datasets whose sizes require pagination.