SQL Server 2012 “Denali”: Paging feature

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. 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

Listing 1

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 

Listing 2

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. 

Happy Learning!!!

If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks

Thanks,

@ahmad_4u