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.

Figure: Creating a new database
Name the database as "Dummy" as below:

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
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
Paginatation 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 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
PaginationBefore2012 1,10

Figure: Records from 1 to 10
Similarly to get values from 11 to 20 we pass the numbers as below:

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

Figure: First 10 Records
To get the next 10 records we need to pass the value 11.

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.
Pagination in 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
Now to get the first 10 records, pass the @start value as 0; as in:

Figure: First 10 records in SQL Server 2012
To get the next 10 records, pass the @start value as 10.

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.
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.