Some times we may be in the need of assigning row numbers in the returned result of an SQL query. Since, Microsoft SQL server 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.

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

SELECT * FROM #Temp ORDER BY RowNumber

DROP TABLE #Temp

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 IDENTITY(int,100,2).

Obviously this can also be done with MS 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 we 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 the SQL Server 2005 also introduces the function rank(). This can be used to rank the records based on another column. En 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 their 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.