Microsoft® SQL Server 2012 Unleashed

Using Row Numbers for Paging Results

Two of the common uses for row numbers are for paging through the results of a query and for selecting a specific subset of rows from within the result set. Essentially, given a page size in terms of number of rows, and a page number, you can return the rows that belong to that given page.

Note

While these techniques for paging results still work in SQL Server 2012 and earlier versions, you may want to look into the new OFFSET and FETCH clauses that have been added to the ORDER BY clause in SQL Server 2012. See Chapter 44, “What’s New for Transact-SQL in SQL Server 2012,” for more information on using these new features.

For example, suppose that you want to return the second page of rows from a query ...