A great new feature of SQL Server 2005 is the ROW_NUMBER function.  In previous versions of SQL to get a numbered row would pretty much require moving the data into a temp table with an identity column, placing an identity column on the original table, or looping through data with a cursor.

SQL Server 2005 makes adding a row number to a query a very simple task with the ROW_NUMBER function.  To use this, simply write the query like normal, say Sales Orders by Customer.  As one of the columns include the ROW_NUMBER() function with the OVER clause.

SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
    ROW_NUMBER() OVER (ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
    INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID

The ORDER BY clause sorts the data, in this case by the OrderDate. The results will now return a sequential number based on the OrderDate along with the CustomerID, OrderDate, and AccountNumber. 

Another question that might come up is if we wanted to number each row by customer and then start over at the next customer.  To do this, the partition attribute must be used as well.

SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
    ROW_NUMBER() OVER (PARTITION BY SO.CustomerID ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
    INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID

Now the data contains a rownumber (OrderNumber) that resets for each customer.

The results of the two queries are seen below: