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


Page through SQL Server results with the ROW_NUMBER() Function

By:   |   Updated: 2007-02-08   |   Comments   |   Related: More > Paging


For many applications searching and providing a subset of a large result set is the core application functionality.  Often the result sets are large and it is a resource intensive process to gather and display the data.  This is because the same query is issued each time a new page is rendered, but a different portion of the result set is displayed.  Once this technique becomes too much of a performance burden, custom solutions are built to meet the need.  With all of the improvements in SQL Server 2005, does a simpler and less resource intensive approach exist to efficiently page through a large result set?


Yes - SQL Server 2005 now ships with the ROW_NUMBER function which is directly supported in T-SQL.  The ROW_NUMBER function provides the ability to issue a query and just return a subset of the result set.  This is achieved by using the partition clause and order by clause of the ROW_NUMBER function.   In the example below, the partition clause is RowNumber and the order by clause is the OrderDate.  The OVER clause is used to determine the partitioning and ordering of the intermediary result set before the ROW_NUMBER function is applied.  This is wrapped by the WITH clause which is being used as a common table expression (CTE) for a temporary named result set.  With this being said, in the last SELECT statement the CTE is queried and only rows 50 to 60 are returned.

USE AdventureWorks;
WITH OrderedOrders AS 
   SELECT SalesOrderID, OrderDate, 
   ROW_NUMBER() OVER (order by OrderDate) AS 'RowNumber' 
   FROM Sales.SalesOrderHeader
FROM OrderedOrders 
WHERE RowNumber between 50 and 60;

Source - SQL Server 2005 Books Online - ROW_NUMBER (Transact-SQL)

The example above is simple and straight forward.  This code can be greatly enhanced by passing in parameters and further customized to meet your specific paging needs, see the related articles in the 'Next Steps' section below.  Although an initial usage of this code would be for paging through records in a web based application, think about some of the application challenges you have faced and consider the ROW_NUMBER function as a simple means to address these issues.

Next Steps

Last Updated: 2007-02-08

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

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.


Learn more about SQL Server tools