SQL paging

The SQL server used will determine they T-SQL query syntax supported and affects the query used for paging queries. Below are examples of a list of SQL server versions and their supported paging queries :

Microsoft SQL 2012 or later

DECLARE @PageNumber INT = 0
DECLARE @RowsPerPage INT = 5
SELECT Id,
	Column1,
	Column2
FROM dbo.NewSQLTable
ORDER BY Id
OFFSET @PageNumber*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

Microsoft SQL 2005/2008 using CTE or later

DECLARE @startRow INT
DECLARE @endRow INT
SET @startRow = 1
SET @endRow = 5
SELECT Id,Column1,Column2
FROM
(
	SELECT ROW_NUMBER() OVER(ORDER BY Id) AS RowNum,
		Id,
		Column1,
		Column2
	FROM dbo.NewSQLTable
) NewSQLTableCTE
WHERE RowNum <= @startRow AND RowNum >= @endRow
ORDER BY Id
GO;

Leave a Reply

Your email address will not be published. Required fields are marked *