Pagination is a common requirement in many applications that deal with large datasets. It allows users to view data in smaller, manageable chunks rather than loading all the data at once. In SQL Server, pagination can be achieved using various techniques depending on the version of SQL Server being used.

Pagination in SQL Server 2000

In SQL Server 2000, pagination can be achieved by using temporary tables and the ROW_NUMBER function. Here’s an example:

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140

CREATE TABLE #tables (RowNumber INT IDENTITY(1,1),
                FirstName VARCHAR(100), LastName VARCHAR(100),
                EmailAddress VARCHAR(100))

INSERT INTO #tables (FirstName, LastName, EmailAddress)
    SELECT PC.FirstName, PC.LastName, PC.EmailAddress
    FROM Person.Contact PC
    ORDER BY FirstName, LastName, EmailAddress

SELECT FirstName, LastName, EmailAddress
    FROM #tables
    WHERE RowNumber > @StartRow
        AND RowNumber < @EndRow

DROP TABLE #tables
GO

Pagination in SQL Server 2005

In SQL Server 2005, the ROW_NUMBER function can be used directly in the query to achieve pagination. Here’s an example:

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140

SELECT FirstName, LastName, EmailAddress
    FROM (
    SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
                ROW_NUMBER() OVER(
            ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
        FROM Person.Contact PC) PersonContact
    WHERE RowNumber > @StartRow
        AND RowNumber < @EndRow
    ORDER BY FirstName, LastName, EmailAddress
GO

Pagination in SQL Server 2012 and onwards

Starting from SQL Server 2012, a more efficient method of pagination is available using the OFFSET and FETCH NEXT clauses. Here’s an example:

USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET @PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
GO

These examples demonstrate different approaches to achieve pagination in SQL Server. It is important to choose the appropriate method based on the version of SQL Server being used to ensure optimal performance.

For further reading, you can refer to the following articles:

• SQL SERVER – Tricks for Row Offset and Paging in Various Versions of SQL Server
• SQL SERVER – Server Side Paging in SQL Server 2012 Performance Comparison
• SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER ( )) – CTE vs. Derived Table
• SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005
• SQL SERVER – Server Side Paging in SQL Server 2012 – A Better Alternative
• SQL SERVER – Row Offset in SQL Server For Different Version