Introduction
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
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 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.
History
- 17th August, 2009: Initial post
