Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          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:   |   Read Comments   |   Related Tips: 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



Last Update:






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

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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools