Search This Blog

Wednesday, November 13, 2013

Paging in SQL Server 2012

As we all know that MS has given us the FETCH NEXT clause in the query to apply paging. Let me explain this feature by adding a temp table of 1 million records.

with wcte as (
Select Row_number()over (order by as RNO
from sys.columns a, sys.columns b, sys.columns c
) Select rno , 'Name: ' + CAST(rno as varchar(10))  as Name
into tblTest
from wcte where RNO <= 1000000

Add primary key on RNO column through the designer. This will add a clustered index on this column.

Now, lets write the query for the SQL Server 2005 / 2008 / 2008 R2 using CTE;

;With wcte as (
Select Row_Number() over (Order by Rno) Rno2,* from tblTest WHERE Name is Not Null
) Select * from wcte where rno2 > 899979 and rno2 < 900000
order by rno

 If you look at the execution plan of this query, it will be like this;

Now lets write the query for SQL Server 2012 paging;

Declare @vPageNo int
Declare @vOffset int
Declare @vPageSize int

Set @vPageNo = 45000
Set @vPageSize = 20
Set @vOffset = (@vPageNo-1) * @vPageSize

FROM tblTest
WHERE Name is Not Null
   OFFSET @vOffset ROWS

I have added the variables for passing OFFSET and PAGESIZE. This gives an additional hint for passing these from the front end as Input parameters of stored procedure.

The execution plan for this query is;

Now, it is very clear that the paging query of SQL Server 2012 performed very limited functions and performed 100% index scan. The SQL Server 2008 query performed many additional functions which makes it a bit smaller with my 2 column table.

Results might be different if you apply the same scenario directly on the clustered indexed column (RNO of tbltest in my case) instead of applying Row_Number() function. This is not the real time case as you never have IDs in sequence in you db tables as sequences break when you delete or migrate. More importantly, you apply paging on queries with WHERE clause. And you cannot use ID columns for paging in any case with WHERE Clause.

No comments:

Post a Comment