SQL Server 2005: Adding Row Numbers To a SELECT Query Result

If you are a SQL Server 2000 programmer you would be definitely knowing the pain in generating sequence numbers to a SELECT query output. But in SQL Server 2005 it is quite easy. Thanks to Microsoft for introducing a new function called ROW_NUMBER() in SQL Server 2005.

ROW_NUMBER() function of SQL Server 2005 allows us to add sequence numbers to a result set of SELECT query. This function generates numbers starting from 1 and incrementing it for each row in the result set.

Let us see to ROW_NUMBER() in action with an example SQL Server 2005 SQL script.

Create an Employee table and populate it with sample data as shown below.

--Create Employee table

CREATE TABLE Employee

(

EmpId Varchar(10),

EmpName Varchar(25),

EmpSalary Numeric(12,0)

)

--Insert sample records

INSERT INTO Employee

VALUES( 'Emp202', 'Ravi', 2000000)

INSERT INTO Employee

VALUES( 'Emp198', 'Shekar', 678000)

INSERT INTO Employee

VALUES( 'Emp234', 'Karim', 805000)

INSERT INTO Employee

VALUES( 'Emp184', 'John', 975000)

INSERT INTO Employee

VALUES( 'Emp151', 'Suresh', 689000)

INSERT INTO Employee

VALUES( 'Emp151', 'Suresh', 879000)

--Query the table

SELECT EmpId,

EmpName,

EmpSalary

FROM Employee

Use the ROW_NUMBER() function as shown below to assign sequence row numbers to the result set.

SELECT (ROW_NUMBER()

OVER (ORDER BY EmpId) )as RowNumber,

EmpId,

EmpName,

EmpSalary

FROM Employee

The OVER() clause next to the ROW_NUMBER() functions tells the SQL Engine to sort data on the specified column and assign numbers as per the sort results. In the above example, result set is order by EmpId and sequence number are assigned to each row.