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.

Figure 1. Creating a new database

Name the database as "Dummy" as below.

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.