Programming Microsoft® SQL Server® 2012

Server-Side Paging

Returning paged query results was difficult to achieve prior to SQL Server 2005. That release of SQL Server introduced a series of ranking functions, including the ROW_NUMBER function that made it possible to return one page at a time from your query. Let’s first see how to use ROW_NUMBER added in SQL Server 2005 to implement server-side paging, and then you will learn how much easier it is to achieve the same goal using the new OFFSET/FETCH NEXT syntax introduced in SQL Server 2012.

Using ROW_NUMBER

The ROW_NUMBER function, as its name implies, generates a sequential number for each row in the result set returned by your query. The value returned by the ROW_NUMBER function can then be used in an outer query’s WHERE clause to ...