Page through SQL Server results with the ROW_NUMBER() Function

By:   |   Comments   |   Related: More > Paging


Problem

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?

Solution

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;
GO 
WITH OrderedOrders AS 
( 
   SELECT SalesOrderID, OrderDate, 
   ROW_NUMBER() OVER (order by OrderDate) AS 'RowNumber' 
   FROM Sales.SalesOrderHeader
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber between 50 and 60;
GO

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelorís degree from SSU and masterís from UMBC.

View all my tips



Comments For This Article