Pagination in SQL Server

Introduction

SQL Server has a new Paging function which is far easier and provides better performance compared to its predecessors. In this article, we will compare the pagination mechanism between previous versions and how it can be done in SQL Server.

This article assumes that SQL Server is installed on the computer to test the query. Open SQL Server Management Studio and create a dummy database to check the new pagination function.

new-pagination-function

Figure 1. Creating a new database

Name the database as "Dummy" as below.

database-sql-server

Figure 2. Naming new database

Click the "Add" button, and it will create a database called "Dummy". Now create a new table in the database by running the following script.

Example

USE [Dummy]
GO
/****** Object:  Table [dbo].[DummyTable]    Script Date: 10/1/2012 9:00:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DummyTable](
    [DummyID] [int] NOT NULL,
    [Name] [varchar](50) NULL,
    [Details] [varchar](50) NULL,
    CONSTRAINT [PK_DummyTable] PRIMARY KEY CLUSTERED 
    (
        [DummyID] ASC
    ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now we need to insert some 5000 records into the table to check the pagination function in SQL Server.

Run the following script to do that.

Example

DECLARE @count INT = 1;
DECLARE @max INT = 5000;
DELETE FROM DummyTable;
WHILE (@count <= @max)
BEGIN
    INSERT INTO DummyTable (DummyID, Name, Details)
    SELECT @count, 'Name' + CAST(@count AS VARCHAR(5)), 'Details' + CAST(@count AS VARCHAR(5));
    SET @count = @count + 1;
END

This will insert 5000 records into the table.

Pagination in previous SQL Server versions

Common Practice 1

We normally create a pagination control in the UI and pass a start value and end value to the stored procedure to get the records.

Let us see how we would do that in versions prior.

Example

CREATE PROCEDURE PaginationBefore2012
(
    @start INT = 1,
    @end INT = 500
)
AS
BEGIN
    SELECT
        DummyID,
        Name,
        Details
    FROM
        DummyTable
    WHERE
        DummyID BETWEEN @start AND @end
    ORDER BY
        DummyID;
END

To get records from 1 to 10, we execute the procedure as below.

Pagination Before2012 1,10.

Paginatation

Figure 3. Records from 1 to 10

Similarly, to get values from 11 to 20, we pass the numbers as below.

Figure 4. Records from 11 to 20

Common Practice 2

Another way is to use the TOP statement and get the records as below.

Example

CREATE PROCEDURE PaginationBefore2012WithTOP
(
    @start INT = 1
)
AS
BEGIN
    SELECT TOP 10
        DummyID,
        Name,
        Details
    FROM
        DummyTable
    WHERE
        DummyID >= @start
    ORDER BY
        DummyID;
END

If we want to get the first 10 records, then we have to pass the value 1.

First-10-Records

Figure 5. First 10 Records

To get the next 10 records, we need to pass the value 11.

Next-10-records

Figure 6. Next 10 records

Note. To make this more dynamic, we can use Dynamic SQL to get N number of records at a time.

Let us see how we can use the Paging function introduced in the SQL Server version.

Pagination in SQL Server

The Paging Function is part of the SELECT statement as an extension to the ORDER BY clause. The following stored procedure shows the same as what we performed in the preceding two common methods.

Example

CREATE PROCEDURE PaginationWith2012
(
    @start INT = 1
)
AS
BEGIN
    SELECT
        DummyID,
        Name,
        Details
    FROM
        DummyTable
    ORDER BY
        DummyID
    OFFSET @start ROWS
    FETCH NEXT 10 ROWS ONLY;
END

The OFFSET value can be expressed as an integer variable and as the FETCH NEXT Value, which we can make configurable so that we can manage a number of records displayed at a time and then also from the start record number from the UI. This allows the developer to retrieve only a certain range of data from the database. If you compare this with the ROW_NUMBER() function introduced in SQL Server 2008, you can see this script is shorter and more intuitive.

Now to get the first 10 records, pass the @start value as 0; as in.

First-10-records-in-SQL Server

Figure 7. First 10 records in SQL Server

To get the next 10 records, pass the @start value as 10.

Figure 8. Next 10 records

Hope this article is useful for you. Do not forget to use the Paging function introduced in 2012 if you get a chance to avail yourself of greater intuition and flexibility.