Row Number in SQL Server

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 + n2) /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

7 replies
  1. Mohammad
    Mohammad says:

    Hi Plamen Ratchev,
    In SQL Server 2005 we can improve the subquery method by using CROSS APPLY. But when ROW_NUMBR is other method will not appropriate.

    Also we can use IDENTITY INTO function for ranking like this:

    SELECT *, Rank = IDENTITY(int, 1, 1)
    INTO #temp_table
    FROM table_name
    … AND other clauses here

    SELECT * FROM #temp_table

    Reply
  2. Plamen Ratchev
    Plamen Ratchev says:

    Hi Mohammad,

    On SQL Server 2005/2008 really does not make sense to use anything else but ROW_NUMBER. Using subquery with CROSS APPLY will be very inefficient.

    Also, using the IDENTITY function does not guarantee values will be assigned in order. Only using the IDENTITY property of a column guarantees that (assuming insert is performed with ORDER BY).

    Reply
  3. Mohammad
    Mohammad says:

    > Plamen Ratchev said
    Also, using the IDENTITY function does not guarantee values will be assigned in order.
    <<

    No.
    When we use ORDER BY clause in the SELECT INTO statement we will guarantee the order like this:

    SELECT employee_name, employee_address,
    Rank = IDENTITY(int, 1, 1)
    INTO #temp
    FROM Employees
    ORDER BY employee_name ASC;

    SELECT * FROM #temp
    ORDER BY Rank ASC

    Reply
  4. Mayur Jain
    Mayur Jain says:

    Hi Plamen Ratchev,

    Can We retrieve specific row in table, we don,t know how much rows and column in table, we just want to retrive specific row, we only have table name..

    eg. row 5
    table_name – login

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.