Every once in a while there is a need to pull the rows with a row number. Here are a few solutions that I have seen to work well.

Below is a sample table with employees that has employee name and employee address columns:

CREATE TABLE Employees (
 employee_name VARCHAR(50) PRIMARY KEY,
 employee_address VARCHAR(100));
 
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Blake Anderson', '2048 River View Rd.');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Ana Williams', '9055 East Blvd.');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Robert Schmidt', '3400 Windsor Street');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Sarah Reese', '1045 Coral Rd.');

SQL Server 2000 and SQL Server 2005

Using an IDENTITY column and a temporary table

This solution is based on creating a temporary table with IDENTITY column used to provide a row number. This approach provides very good performance. Here are the steps:

-- Create the temp table
CREATE TABLE #EmployeeRowNumber (
 rn INT IDENTITY (1, 1),
 employee_name VARCHAR(50),
 employee_address VARCHAR(100));
 
-- Generate the row number
-- To achieve an ordered list the names are sorted
INSERT #EmployeeRowNumber (employee_name, employee_address)
SELECT employee_name, employee_address
FROM Employees
ORDER BY employee_name;
 
-- Select the row number
SELECT employee_name, employee_address, rn
FROM #EmployeeRowNumber
ORDER BY rn;

  
-- Results

employee_name    employee_address         rn
---------------- ------------------------ -----------
Ana Williams     9055 East Blvd.          1
Blake Anderson   2048 River View Rd.      2
Robert Schmidt   3400 Windsor Street      3
Sarah Reese      1045 Coral Rd.           4

Using a subquery to count the number of rows

This solution is based on using a subquery on a unique column (or combination of columns) to count the number of rows. Here is how it looks with the sample data provided above:

SELECT employee_name, employee_address,
      (SELECT COUNT(*) 
       FROM Employees AS E2
       WHERE E2.employee_name <= E1.employee_name) AS rn  
FROM Employees AS E1  
ORDER BY employee_name;

If the values in the column are not unique then duplicate row numbers will be generated. That can be resolved by adding a tiebreaker column that will guarantee the uniqueness. This approach is a slower method than using an IDENTITY column and a temporary table. Since it will incur (n + n²) /2 row scans it may not be practical to use on a large table.

SQL Server 2005

Using the ROW_NUMBER function

In SQL Server 2005 the new function ROW_NUMBER provides the fastest approach to solve the problem:

SELECT employee_name, employee_address,
       ROW_NUMBER() OVER(ORDER BY employee_name) AS rn
FROM Employees
ORDER BY employee_name;

Additional resources:

How to dynamically number rows in a SELECT Transact-SQL statement: http://support.microsoft.com/kb/186133

SQL Server 2005 Ranking Functions: http://msdn2.microsoft.com/en-us/library/ms189798.aspx

Book: “Inside Microsoft SQL Server 2005: T-SQL Querying” by Itzik Ben-Gan, Lubor Kollar and Dejan Sarka