Exploring ROW_NUMBER() Function

Displaying row number is a tricky task in SQL Server 2000. You probably already know some techniques to solve this problem. The first no tricky method is using auto number field and include it in the SELECT query. But this does not solve the problem at all, because auto number field is often not required in a table.

Some of my friends use cursor to calculate each row number. This is an overkill solution because cursor consumes lot of server resources. I often use sub query that calculate each row number with COUNT agregate function.

Here is the script for displaying row number in Products table:

USE NorthWind
Go
SELECT (
SELECT COUNT(*) FROM Products A WHERE A.ProductID 
Then you will have the following result:
Number ProductName
------ -------------------------------
1      Chai
2      Chang
3      Aniseed Syrup
4      Chef Anton's Cajun Seasoning
5      Chef Anton's Gumbo Mix
6      Grandma's Boysenberry Spread
7.........
..........
The problem of this approach is the query does table scan twice. Fire up the Execution Plan and you will find that SQL Server does table scan in Northwind.Producs B and Northwind.Producs A. It consumes more resources when the result sets are large.
ROW_NUMBER Function in SQL2005
SQL Server 2005 comes with ROW_NUMBER that provide row number capability without sacrifice the server resources with unneeded table scan. This function can be used for various tasks, not only for displaying row number field. With this function on hand, you can solve the above problem with the following statement:
USE NorthWind
Go
SELECT ROW_NUMBER() OVER (ORDER BY ProductID) as RowNumber,
ProductID, ProductName, UnitPrice
FROM Products
This approach need only one table scan instead of twice as the sub query approach of SQL Server 2000. It will return the following result:
RowNumber  ProductID   ProductName                      UnitPrice
---------  ----------- ------------------------------- ---------------------
1           1           Chai                            100.00
2           2           Chang                           100.00
3           3           Aniseed Syrup                   100.00
4           4           Chef Anton's Cajun Seasoning    100.00
Please give attention to the OVER keyword that used as a base for numbering process. That query does numbering based on the order of ProductID field. You will get different result if replace (ORDER BY ProductID) with (ORDER BY ProductID DESC). The different result sets is also returned if you replace ProductID with another field. 
Modify the above query become the following script and hit F5 key:
SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) as RowNumber,
ProductID, ProductName, UnitPrice
FROM Products
It returns the different result with the above query. This time you get result sets that already numbered based on UnitPrice field, with descending order.
Practical Use of ROW_NUMBER
Imagine you want to update the UnitPrice field of the Products table. The sales department wants to update ten most expensive products become 10% less. This puzzle can be solved with ROW_NUMBER function.
Consider the following UPDATE statement:
UPDATE Products
SET UnitPrice = 0.9 * UnitPrice
WHERE ProductID IN
(
SELECT ProductID FROM(
SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS Number, ProductID FROM Products) AS B
WHERE Number 
The script updates ten most expensive products with 0.9 * UnitPrice. You also can do parameterized query, make it more flexible to determine the update criteria.

Custom Paging  in Web Application

Another use of ROW_NUMBER is doing custom paging in ASP.NET application. ASP.NET data control such as old DataGrid (.NET 1.1) and GridView (.NET 2.0) support both default and custom paging. When paging with default method is simple and easy, it needs big overhead. When you navigate between pages with default paging enabled, it returned ALL data on every postback instead of the data that really needed for particular page. This is a big performance issue when dealing with large result sets. 

Custom paging solves the problem by returning only the result sets needed for every page. ASP.NET 2.0 provides ObjectDataSource control that already supports custom paging. 

The first task to do is creating stored procedure with two parameters: pageSize and startRowNumber. The pageSize parameter contains row amount of every page, and startRowNumber is used for storing starting row number for particular page.
CREATE PROC sp_GetProductsByPage
@pageSize int,
@startRowNumber int
AS
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ProductName) as RowNumber, ProductID, ProductName, UnitPrice
FROM Products) AS Products
WHERE RowNumber BETWEEN @startRowNumber AND (@startRowNumber + @pageSize )
The sp_GetProductsByPage procedure will return the only data needed for particular page. ObjectDataSource needs a business object that returns a DataTable, DataSet, or Collection object. So you create a Product class with 2 methods: GetProductsByPage and CountProduct. 
The following code assuming that you already have a valid SqlConnection object.
public DataTable GetProductsByPage(int pageSize, int startRowNumber)
    {
        SqlCommand cmd = new SqlCommand();
        SqlParameter prPageSize = new SqlParameter("@pageSize", SqlDbType.Int, 4);
        cmd.Parameters.Add(prPageSize);
        prPageSize.Value = pageSize;
        SqlParameter prStartRow = new SqlParameter("@startRowNumber", SqlDbType.Int, 4);
        cmd.Parameters.Add(prStartRow);
        prStartRow.Value = startRowNumber;
        cn.Open();
        cmd.CommandText = "sp_GetProductsByPage";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = cn;
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
        return dt;
    }
    public int CountProduct()
    {
        SqlCommand cmd = new SqlCommand();
        cn.Open();
        cmd.CommandText = "SELECT COUNT(ProductID) FROM Products";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = cn;
        return (int)cmd.ExecuteScalar();
    }
The GetProductsByPage function executes sp_GetProductsByPage stored procedure and returns result sets for every page. The CountProduct function is needed by ObjectDataSource for calculating input integer for startRowNumber parameter.
The last action is build a webpage with Visual Web Developer 2005 Express (I love it, it's free!), drag and drop GridView and ObjectDataSource. Configure the ObjectDataSource to use Product class as data object, and choose GetProductsByPage as SELECT command. Skip Update, Insert, and Delete tab, and finish it.
The ObjectDataSource property should be configured as follows:
Don't forget to set up the AllowPaging property of the GridView to TRUE, and PageSize property to 10. Now you have a GridView with custom paging ability, something that more reliable and resource friendly that default paging.
I found that Scott Mitchell has written a good examination of custom paging method and measure the performance comparison with SQL Server Profiler.
Happy Coding 🙂
http://choirulamri.or.id