Solution for SQL Server 2008
ROW_NUMBER function can be used for paging purposes. You assign row numbers to the result rows based on the desired ordering, and then filter the right range of row numbers based on given page-number and page-size arguments.
For optimal performance, you want to have an index defined on the window ordering elements as the index keys (and eventually include in the index the rest of the attributes that appear in the query for coverage purposes).
Solution for SQL Server 2012 or SQL Server 2014
An alternative solution to paging is to use the new
OFFSET/FETCH filtering option: