SQL Server 2012 Server Side Paging

Problem

When designing a User Interface (UI) you normally display 10, 20, 50 or 100 rows on the screen for readability purposes and easy navigation. One way to do this, is to bring all the data from SQL Server to local memory and do the navigation locally. This approach looks simple, but has a couple of issues. First, it could choke the network during a huge data transfer from the server to the local machine (or machine running UI). Second, you could need much more memory to accommodate the data locally. Third, when you navigate to the next page you are actually viewing data stored locally and you will not be able to see updated records on the server until you re-fetch all the data again. So what are some options for server side paging and how does SQL Server 2012improves on this process? Check out this tip to learn more.

Solution

Even before SQL Server 2012, server side pagination was possible using Common Table Expression, ROW_NUMBER function, etc. The problem with this approach is, first writing this code is a little tricky and second we need to use the ORDER BY clause multiple times resulting in multiple SORT operators, which becoming costly from a performance perspective. With SQL Server 2012, a simple and elegant way of ad-hoc server side paging has been introduced which also appears to be less expensive from a performance perspective than previous techniques.

In this tip, I will briefly demonstrate how you can achieve server side paging using the ROW_NUMBER function. Next, I will outline the improved ORDER BY clause with the OFFSET…FETCH NEXT commands in SQL Server 2012. Our final example will compare of both of these approaches.

In Script #1, I am simply using the ROW_NUMBER function to apply row numbering in the result set and I am also using the WHERE clause to restrict the result set to specific set of records. In this case I want rows from 41 to 50, that is to say page 5 when the page size is 10 rows, and hence I have specified @DesiredPageNumber = 5. If you want rows from 51 to 60, that is page 6, you just need to specify @DesiredPageNumber = 6 and so on.

USE AdventureWorks

GO

DECLARE @NumberOfRows INT = 10 — Number of rows you want on a page

DECLARE @DesiredPageNumber INT = 5 — Page number

SELECT *

FROM

  (

  SELECT RowNum = ROW_NUMBER() OVER (

    ORDER BY EmployeeID), *

   FROM HumanResources.Employee

  ) AS a

WHERE RowNum > (@NumberOfRows * (@DesiredPageNumber – 1))

AND RowNum <= (@NumberOfRows * (@DesiredPageNumber – 1)) + @NumberOfRows

ORDER BY EmployeeID

GO

In Script #2, I am using the new and improved ORDER BY clause in SQL Server 2012 (for more information about improved ORDER BY clause in SQL Server 2012 click here) with OFFSET … ROW/ROWS and FETCH FIRST/NEXT … ROW/ROWS ONLY clauses. With the OFFSET clause, we instruct the Query Engine to consider the number of rows for skipping before it starts returning rows whereas with the FETCH clause we specify the number of rows to return after the OFFSET clause has been processed. For both OFFSET and FETCH, the specified value can be an integer constant or an expression that is greater than or equal to zero. This query returns a result set similar to the query in Script #1. In this code, you can also specify the number of rows you want in a page and the page number for which you want the result set, but as you can notice this query is very simple and straight forward to write.

USE AdventureWorks

GO

DECLARE @NumberOfRows INT = 10 — Number of rows you want on a page

DECLARE @DesiredPageNumber INT = 5 — Page number

SELECT *

FROM HumanResources.Employee

ORDER BY EmployeeID

OFFSET (@NumberOfRows * (@DesiredPageNumber – 1)) ROWS

FETCH NEXT @NumberOfRows ROWS ONLY;

GO

In Script #1 and Script #2, we saw two different methods to write the same query and how the second query, with OFFSET and FETCH, makes the developer’s life easier. But what about performance? How they will be rated in terms of performance? I did a very simple test on very small set of data and the comparison is provided in Script #3. As you can see, the relative cost of the first query (which uses the SORT physical operator) is 77% opposed to just 23% of second query. Please note, I did a very small test, the result may vary depending on your environment, amount of data involved, the way you write your query, etc.

USE AdventureWorks

GO

DECLARE @NumberOfRows INT = 10 — Number of rows you want on a page

DECLARE @DesiredPageNumber INT = 5 — Page number

SELECT *

  FROM

    (SELECT RowNum = ROW_NUMBER() OVER (ORDER BY EmployeeID), *

      FROM HumanResources.Employee

    ) AS a

  WHERE RowNum > (@NumberOfRows * (@DesiredPageNumber – 1))

  AND RowNum <= (@NumberOfRows * (@DesiredPageNumber – 1)) + @NumberOfRows

  ORDER BY EmployeeID

SELECT *

FROM HumanResources.Employee

ORDER BY EmployeeID

        OFFSET (@NumberOfRows * (@DesiredPageNumber – 1)) ROWS

FETCH NEXT @NumberOfRows ROWS ONLY;

GO

Notes

• OFFESET and FETCH clauses are part of ORDER BY clause and hence you need to specify an ORDER BY clause when using the OFFSET and FETCH clauses.
• The sample code, example and UI is based on SQL Server 2012 CTP 1, it might change in further CTPs or in the final/RTM release.

Next Steps

• Review Common Table Expression related tips.
• Review Paging through SQL Server result sets with the ROW_NUMBER() Function tip.
• Review new improved ORDER BY clause in SQL Server 2012.