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.

How to do Pagination in SQL Server? - Interview Question of the Week #111 Pagination-800x599

Solarwinds

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.

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

Solarwinds
, ,
Previous Post
How to Keep Certain Records on Top While Ordering Data? – Interview Question of the Week #110
Next Post
Why to Use SQL Server Configuration Manager Over Services applet (services.msc)? – Interview Question of the Week #112

Related Posts

6 Comments. Leave new

Leave a Reply

Menu