Paging in SQL Server 2014

 

Designing Query paging in SQL Server 2014
 
Recently, I have been working on SQL Server paging and realized that the latest way of paging query of SQL Server 2014 has great performance when compared to the classical or custom query that we use for paging.
 
In this article I am depicting both the old way of SQL Paging and how query paging works in SQL Server 2014 and difference between both.
 
Before we start with SQL Query on Paging lets create a sample demo table which insert 10K rows in a table.


create table pageTbl
(id int identity(1,1) not null primary key,
name varchar(25) not null,
dt datetime default getdate()
)
 
Insert 10 K rows in the demo table "pageTbl".
 
insert into pageTbl (name) values ('item' + convert(varchar,isnull(@@identity,0)))
go 10000
 
Get the count to verify whether 10K rows has been inserted.
select count(1) from pageTblGet the top 10 records to verify
select top 10 * from pageTbl 

 
You can see that the rows are inserted into the table with different item number.
 
The old way of paging is SQL Server is being done as shown below, 
declare two variables which hold page number and other for page size and
we create a virual row ROW_NUMBER which has an row count to order the rows and we use an order by clause to order the rows and few page calculations which used to display the relevant rows. 
declare @pagenum as int, @rowpage as int

set @pagenum = 2
set @rowpage = 10

select * from (select ROW_NUMBER() over (order by id) as number, name, dt from pageTbl) as mydata
where number between ((@pagenum - 1) * @rowpage + 1) and (@pagenum * @rowpage) order by number
 
If we run the above query the result will be shown as below, this will display rows from 11 to 20 as we need data for page 2 and the page size is 10.

The above query has been enhanced in of SQL Server 2012 or later versions.

declare @pagenum int, @rowspage int
set @pagenum = 2
set @rowspage = 20
select id, name, dt from pageTbl order by id offset((@pagenum-1) * @rowspage) rows fetch next @rowspage rows only

 



If you take a look at the Query execution plan between two queries, there is an filter which costs around 2% with the classical way. First it do an assignment, filter, top, segment and then it do the index scan.



 



 

With the latest way if you see the execution plan there will be an assignment and then it directly scan the index which has a faster way of doing paging. The cost is reduced dramatically when taking large number of records into consideration and the performance also varies.

 

This is the way you can do paging in SQL Server 2014.

 

Happy coding!