SQL Server 2012 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 2012.
This article assumes that SQL Server 2012 is installed in the computer to test the query. Open SQL Server 2012 Management studio and create a dummy database to check the new pagination function.
![new-pagination-function.jpg]()
Figure: Creating a new database
Name the database as "Dummy" as below:
![database-sql-server2012.jpg]()
Figure: 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:
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 2012.
Run the following script to do that:
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.
Paginatation in previous 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 to 2012:
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:
PaginationBefore2012 1,10
![Paginatation.jpg]()
Figure: Records from 1 to 10
Similarly to get values from 11 to 20 we pass the numbers as below:
![Records-from-11-to-20.jpg]()
Figure: Records from 11 to 20
Common Practice 2
Another way is to use the TOP… statement and get the records as below:
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.jpg]()
Figure: First 10 Records
To get the next 10 records we need to pass the value 11.
![Next-10-records.jpg]()
Figure: 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 SQL Server version 2012.
SQL Server 2012
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:
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 so as FETCH NEXT Value which we can make them 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 2012.jpg]()
Figure: First 10 records in SQL Server 2012
To get the next 10 records, pass the @start value as 10.
![Next-10-records-in-SQL-Server2012.jpg]()
Figure: 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 the greater intuition and flexibility.