The.Long.Awaited.Feature

I've recently downloaded SQL Server 2005 April CTP from MSDN to take a look. One new feature that immediately caught my attention was the ROW_NUMBER() function. For all these years, I have always wished that SQL Server could provide a way to extract a range of rows from a result set. Afterall, Oracle has it, SAS has it but why not SQL Server?

Looks like my wish is finally granted in SQL Server 2005. The ROW_NUMBER() function returns a sequential row number for each row in a result set more or less like Oracle's ROWNUM. Here's how it looks like:

SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNumber, *
  FROM Production.Product

The example above gives a result set with a column named RowNumber that contains the corresponding sequential row number of each row. The OVER statement is used to determine the order of the results in this example.

Now that we have the row numbers, we can easily slice out a page of rows. However, it is unfortunate that we cannot simply apply a WHERE statement to the above code. Instead, we will need another query to achieve it. Here's the revised code:

SELECT * FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNumber, *
       FROM Production.Product
) AS Product
WHERE Product.RowNumber BETWEEN 100 AND 120

The result of the code will show 21 rows - from 100th to 120th. With this feature, we should be able to write proper paging code in our applications without resorting to complex WHERE statements and temporary tables. Applause to the SQL Server Team.