Pagination is a regular technique used for developing web applications. There are numerous methods of implementing pagination. Below, I'm creating a list of methods that will allow both a SQL Server developer and web developer.
/**Replace X & y with relevant numbers
SELECT * FROM (
SELECT TOP x * FROM (
SELECT TOP y fields
ORDER BY table.field ASC) as foo
ORDER by field DESC) as bar
ORDER by field ASC
Republished from http://www.sqlserver-dba.com.