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.
While these techniques for paging results still work in SQL Server 2012 and earlier versions, you may want to look into the new
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 ...