Page through SQL Server results with the ROW_NUMBER() Function

By: Jeremy Kadlec   |   Updated: 2007-02-08   |   Comments   |   Related: > 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

• For some organizations which build and support web based applications that have an intense search capability, this single function, ROW_NUMBER, can be motivation enough to migrate to SQL Server 2005.
• Test the performance and ease of use to use the ROW_NUMBER function as compared to your current coding technique.
• Check out these related articles:
  • ROW_NUMBER (Transact-SQL)
  • OVER Clause (Transact-SQL)
  • RANK (Transact-SQL)
  • DENSE_RANK (Transact-SQL)
  • NTILE (Transact-SQL)
  • Functions (Transact-SQL)
• Check out the following tips on MSSQLTips.com:
  • Categories - Scripts and T-SQL
  • Recursive queries with SQL Server 2000
  • Cross tab queries with SQL Server 2000
  • Dynamic SQL
  • Getting a RowCount Without doing a Table Scan
  • SQL Server Randomly Retrieve Records

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips