A look at the ROW_NUMBER keyword in SQL 2005
In a previous post I talked about the changes to the TOP keyword in SQL 2005. Keeping along the same lines, this post will focus on one of the T-SQL enhancements that I am most excited about using, the ROW_NUMBER keyword.
All of us at some point in time worked on an application where we had to display a bunch of data in a paged format. There were a few options: bring back all the data and only display the records you want, build a dynamic query bring back only the rows you want, or use Temp tables in a Stored Procedure to bring back only the rows you want. Each option had its benefits and drawbacks, but none of them for particularly elegant or efficient. All that gets much easier with the addition of the ROW_NUMBER keyword.
The syntax of the ROW_NUMBER function is:
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Let’s look at this with a few quick examples:
First a real basic example. In this example, we are only using the “ORDER BY” clause of the function. Remember, that this ORDER BY specified in the OVER clause applies only to the ROW_NUMBER function. You can specify a different ORDER BY for the final result if you wish.
--Basic
Select ROW_NUMBER () OVER(Order By OrderDate ASC, TotalDue DESC) as RowNum, TotalDue, *
From Sales.SalesOrderHeader
Where OrderDate BETWEEN '6/6/2004' AND '6/8/2004'
Order By RowNum
In the next example, we will utilize the PARTITION clause. This is not as useful when we talk about the paging application, but it could be very useful if you are looking at sets of data. The PARTITION acts as a grouping mechanism for the ROW_NUMBER function. When specified, the ROW_NUMBER clause will restarting numbering at the beginning of each group as specified by the PARTITION clause. So in this case it will assign Row Numbers based on the Total Due within each distinct Order Date, Territory ID combination.
-- Using the Partition we can have a Row Number within sections
Select ROW_NUMBER () OVER(PARTITION BY OrderDate, TerritoryID Order By TotalDue DESC)
as RowNum, OrderDate, TerritoryID, TotalDue, *
From Sales.SalesOrderHeader soh
Where OrderDate BETWEEN '6/6/2004' AND '6/8/2004'
Order By soh.OrderDate, soh.TerritoryID
On to our original question. How does this function help us our paging application example? I’m glad you asked. The below query takes in an input parameter of a page number and number of rows per page and returns only the records that would be displayed on that page. So in this case it brings back rows 76 through 90. I think this is really cool because not only is it code that I had to write myself in the past, but in the case where I might have been bringing back a large number of rows, I am limiting the amount of data that I am sending from my database server to my application tier.
-- We can also use the RowNumber to select a subset of Rows
DECLARE @PageNumber int
DECLARE @PageSize int
SET @PageNumber = 6;
SET @PageSize = 15;
WITH OrderedSales AS
(Select ROW_NUMBER () OVER(Order By OrderDate ASC, TotalDue DESC) as RowNum,
TotalDue, CustomerID, SalesOrderID, OrderDate From Sales.SalesOrderHeader
Where OrderDate BETWEEN '6/6/2004' AND '6/8/2004')
Select * From OrderedSales
WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
Order By RowNum
You may have noticed the “WITH” keyword that I am using in the above query. The WITH is a common table expression (CTE). I will cover that in a future post. I hope this post was helpful in giving you an introduction to the ROW_NUMBER() function.
Now playing: Killers - Mr. Brightside