Microsoft® SQL Server 2012 Unleashed

Ad Hoc Query Paging

A common need of many end user applications, specifically web-based applications, is the need to use paging to show a set number of rows per page and allow the user to scroll through each page of results a chunk at a time rather than download the entire set with the initial query. Various techniques (temporary tables, common table expressions, ranking functions, and so on) have been employed to provide this ability in SQL Server, but most required somewhat complicated SQL code to implement.

SQL Server 2012 provides a mechanism to implement paging using the relatively simple syntax provided by the ANSI standard ORDER BY ... OFFSET / FETCH clause. The ORDER BY ... OFFSET / FETCH clause allows you to specify a range of rows ...