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:
- Check out the following tips on MSSQLTips.com:
