Paging is a process of splitting large result set into subset of pages. So, instead of displaying 5000 records resulting from a search, first 20 records are shown and then next 20 and so on.
In SQL Server 2005, Common Table Expression (CTE) is generally used to implement paging feature. In SQL Server 2012, MS has come up with a modified ORDER BY clause to implement the same. This is done using two new keywords OFFSET and FETCH. Offset tells the number of rows to skip and Fetch tells number of rows to be returned after offset clause is processed. So, offset 0, Fetch 10 will return 10 rows starting from 1 and an offset 2, Fetch 10 will return 10 rows starting from 3.
This is similar to using Limit operator in MySql.
The code in Listing 1 illustrates how it is done via Order By clause.
DECLARE @Start INT=0, @PageLimit INT=10; SELECT emp.empid, emp.firstname, emp.lastname, emp.phonenumber, dpt.departmentname, dsg.designation, addr.addline1, addr.state FROM tblemployee emp JOIN tbldepartment dpt ON emp.deptid = dpt.deptid JOIN tbldesignation dsg ON dsg.designationid = emp.designationid JOIN tbladdress addr ON addr.empid = emp.empid ORDER BY lastname ASC,firstname ASC OFFSET @Start ROW FETCH NEXT @PageLimit ROWS ONLY
The above query returns 10 rows starting from 1 to 10. To get next 10 rows, set the @start parameter to 10 and so on.
With that being said, let’s compare the performance of this new feature with paging through CTE. The code in listing 2 shows paging via CTE.
DECLARE @PageNumber INT=1, @PageSize INT=10; WITH ctepaging AS (SELECT emp.empid, emp.firstname, emp.lastname, emp.phonenumber, dpt.departmentname, dsg.designation, addr.addline1, addr.state, Row_number() OVER(ORDER BY lastname, firstname) AS rownum FROM tblemployee emp JOIN tbldepartment dpt ON emp.deptid = dpt.deptid JOIN tbldesignation dsg ON dsg.designationid = emp.designationid JOIN tbladdress addr ON addr.empid = emp.empid) SELECT * FROM ctepaging WHERE rownum BETWEEN ( @PageNumber - 1 ) * @PageSize + 1 AND @PageNumber * @PageSize
The query in listing 1 took 424 ms where as the query in listing 2 executed in 457 ms. So, there isn’t any significant improvement in performance when using offset and fetch for paging. Moreover, the execution plans of both the queries are very much similar. Figure 1 and Figure 2 show execution plan for queries in listing 1 and listing 2 respectively. The only difference are the Segment, Sequence Container and filter operator which are being used to filter out rows using row count and they don’t add much to the query cost.
Figure 1: Execution Plan for paging using offset and fetch
Figure 2: Execution Plan for paging using a CTE
In addition to do paging, offset and fetch keyword can be used to replace top operator as shown in below query.
SELECT emp.empid, emp.firstname, emp.lastname, emp.phonenumber FROM tblemployee emp ORDER BY lastname ASC, firstname ASC OFFSET 0 ROW FETCH NEXT 10 ROWS ONLY
The performance is similar to what we get with top keyword as the top operator is used to filter out the rows as shown in the below execution plan.
More information regarding this can be found at http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx#Offset.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
2 Comments on “SQL Server 2012 – “Denali” Paging feature”
Have you looked at memory use with those queries? I mean, I would hope that the Segment / Sequence Container stuff would make a difference there, even if the speed is the same.
Thanks Joel for pointing out the case… I didn’t.. will check and update.