Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2012 Server Side Paging


By:   |   Last Updated: 2011-07-07   |   Comments (3)   |   Related Tips: More > 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.

Script #1 - Ad-hoc server side pagination with ROW_NUMBER Function

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.

Script #2 - Ad-hoc server side pagination with ROW_NUMBER Function

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.

Script #3 - Ad-hoc server side pagination comparison
ROW_NUMBER Function vs. OFFSET and FETCH clauses

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



comparison of performance for server side paging with sql server denali

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


Last Updated: 2011-07-07


get scripts

next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, February 08, 2016 - 1:34:06 AM - ajay Back To Top

 how to perform pagination at run time query like 

select * from employee.

or  

some time query will be

select * from employee where sal>5000

 

i have a custome query editor where user can write custom query and he can fire to database 

so we don't know the column name so we can not user OVER .so i am unable to use pagination.

please sugges me some tips how can i do.


Saturday, July 09, 2011 - 10:19:31 AM - Armando Prato Back To Top

Very, very cool!

 

While I do see a more efficient query plan for the Denali version, I'm curious as to what the CPU and logical read differences were between the 2 approaches.   Were you able to discern a marked improvement with those metrics?

Also, I'd be curious as to how they would perform with large sets of data (hundreds of thousands of rows).  My experience with the "old way" is that the higher the page number requested, the slower the response time (as more rows being read in to figure out what the next page should look like)

 

Thanks!


Thursday, July 07, 2011 - 8:51:13 AM - Luke Campbell Back To Top

Great tip!!  I'm glad to see this is being included in the next version of SQL Server.  Thanks for sharing.


Learn more about SQL Server tools