Part 2 of server side paging in SQL Server Denali: a reader keeps rows per page and page number in a table, and OFFSET and FETCH can read them from there.
Pagination With OFFSET and FETCH
OFFSET and FETCH make page-number queries readable, but deep pages scan past prior rows; keyset pagination keeps the next page focused.
Keyset Pagination: Next and Previous Pages Without OFFSET
Build keyset pagination with a matching index, unique tie breaker, next and previous queries, and a fair deep-page comparison with OFFSET.
SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table
Paging in SQL Server 2005 with ROW_NUMBER: CTE vs. derived table. I compare both methods and show they give the same performance for common queries.