Calculating Row Number in Sql Server 2000

During work in the sql server 2005 many of the new thing has been found in the sql server one of which is the ROW_NUMBER() is a new function that is added to the SQL Server 2005 T-SQL syntax. ROW_NUMBER() is used to assign ranks to the result of a query. Here is the sample output of using the Row_Number, the first column contains the serialNumber.

Here is the simple t-sql statements which are used to perform same kind of functionality as the Row_Number function in the sql server 2005.

Use NorthWind

Declare @TotalRecord INT
Set @TotalRecord=(Select Count(*) from Employees )

Select @TotalRecord-(Select Count(*) from Employees where  Employee.EmployeeID < EmployeeID) as SerialNumber,
EmployeeName=FirstName+' '+LastName, Title, HireDate, Country from Employees AS Employee

In the above t-sql statement I have declare variable which is used to save the total number of records which is the @TotalRecord. And I have used it in the select statement to subtract the number of record remaining of the current record.

I have used the northwind database and in that database I have used the employees table. Hope you like this post and get some knowledge from this post.

All and any comments / bugs / suggestions are welcomed!