SQL Server 2005 ROW_NUMBER()
Knowing the row number for a row in a result set has many uses. Paging through result sets is one example. In previous versions of SQL Server getting the row number for a result set was less than straightforward, using temporary tables or incremental counts based on a hopefully unique column were common solutions.
SQL Server 2005 makes this a lot more straightforward. The ROW_NUMBER() function provides a easy way to return the sequential number of a row within a result set. The syntax is shown below -
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
A simple example using the AdventureWorks sample database...
SELECT ROWID= ROW_NUMBER() OVER ( ORDER BY ProductID ASC), ProductID, [Name]
FROM Production.Product
...returns...
