Click here to Skip to main content
15,745,270 members
Articles / Database Development / SQL Server
Posted 17 Aug 2009


20 bookmarked

Row Numbers in SQL Query (Microsoft SQL Server 2000 and 2005)

Rate me:
Please Sign up or sign in to vote.
4.82/5 (11 votes)
17 Aug 2009CPOL2 min read
Helps in assigning row numbers in the returned result of an SQL query


Sometimes we may need to assign row numbers in the returned result of an SQL query. Since Microsoft SQL server 2000 doesn't support row number function, it has to be achieved through the introduction of IDENTITY column when we use ‘into’ clause in the select statement. The temp table can be populated first. Finally, the temp table can be used to fetch the rows along with the field row number.

Using the Code

The following example demonstrates the method of using temp table to assign row numbers to the result of a select query. Remember to drop the temp table after the select statement; otherwise, it will throw an error indicating the existence of the temp table already when you execute this query next time.

SELECT IDENTITY(int, 1,1) AS RowNumber, EmployeeId INTO #temp _
	FROM EmployeeMaster ORDER BY EmployeeId ASC

The IDENTITY gets three mandatory parameters, namely datatype of the identity column, starting value and the increment. With these, you can customize the new column according to your requirement. For example, an integer starting from 100 and incremented for each row with 2 can be specified as:


Obviously, this can be done with Microsoft SQL Server 2005. But the SQL server introduces a new function called row_number(). This function assigns a unique row number starting from 1 to number of records.

The following is the statement which is the SQL Server 2005 equivalent of the above query set:

SELECT row_number() OVER (ORDER BY EmployeeId) AS RowNumber,  _
   EmployeeID FROM EmployeeMaster

This statement doesn't use the temp table. So, the fetch will be faster than the previous approach. As in the previous approach, if you want to start the row number from 100, you can use:

row_number() OVER (ORDER BY EmployeeId)+100 as RowNumber

In addition to the row_number() function, SQL Server 2005 also introduces the function rank(). This can be used to rank the records based on another column. An example for the usage of rank function is as follows:

SELECT rank() OVER (ORDER BY JoinDate) AS RowNumber, _
	EmployeeID, JoinDate FROM EmployeeMaster

The above statement gets the rank of each employee based on his/her join date as a new column. If that column is the Key column (such as the EmployeeId), then the row_number() and rank() both produce the same result.


  • 17th August, 2009: Initial post 


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior) Infence Technologies
India India
Ganesh Kumar has done his Bachelor's degree in Computer Science at Bharathiar University, Coimbatore, Master's degree in Computer Applications at IGNOU and M.Tech in Computer Science & Engineering at Dr. MGR University, Chennai, India.

He is having hands-on experience in Algorithm implementation, Application design/development for Servers, Desktop and Single Board Computer devices in .Net C#, C, C++.

Interested in developing & customizing algorithms.

His hobbies include Blogging, Listening to Music and Singing.


Comments and Discussions

GeneralMy vote of 5 Pin
Herman M. Reyes20-May-11 9:08
Herman M. Reyes20-May-11 9:08 
I have been looking this issue for almost a year, luckily i joined code project.. excellent!!!!
GeneralError in SQL Server 2000 with [modified] Pin
Massimo Conti14-Oct-10 5:05
Massimo Conti14-Oct-10 5:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.