How to do Pagination in SQL Server? – Interview Question of the Week #111

Question: How to do Pagination in SQL Server?

Answer: A very popular question during interviews, which often stumps the candidate.

I have helped many organizations to hire the right talent for many years and I know that this one question often throws off DBA and developers.

The very common practice of the developers is to retrieve all the data in the page and then query each data one at a time. This is not efficient at all and creates lots of unnecessary load on the server. Let us see a method page pagination in SQL Server.

My preferred method of pagination is listed in the SQL Server 2012+ section as that is the most efficient in terms of performance.

Pagination in SQL Server 2000

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

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 / 2014 / 2016 onwards

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

Here are a few additional articles you can read to strengthen your understanding.

• 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

Reference: Pinal Dave (https://blog.sqlauthority.com)