SQL Server 2008
The ROW_NUMBER function can assign an incrementing number to each row in a result set. Combined with a Common Table Expression that uses a BETWEEN operator, it is possible to create 'pages' of result sets. For example: page one containing results 1-10, page two containing results 11...
SQL Server 2012
The OFFSET FETCH clause implements pagination in a more concise manner. With it, it's possible to skip N1 rows (specified in OFFSET) and return the next N2 rows (specified in FETCH):
SELECT *
FROM sys.objects
ORDER BY object_id
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY
The ORDER...
In earlier versions of SQL Server, developers had to use double sorting combined with the TOP keyword to return rows in a page:
SELECT TOP 10 *
FROM
(
SELECT
TOP 50 object_id,
name,
type,
create_date
FROM sys.objects
ORDER BY name ASC
) AS data
...
SQL Server 2012 / 2014
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM OrderDetail
ORDER BY OrderId
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
SQL Server 2005/2008/R2
DECLARE @RowsPerPage INT = 10, @PageNumber INT = ...
For getting the next 10 rows just run this query:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Key points to consider when using it:
ORDER BY is mandatory to use OFFSET and FETCH clause.
OFFSET clause is mandatory with FETCH. You can never use, ORDER BY …
FETC...
