Thursday, December 02, 2004

Row_Number function in Sql Server 2005

In this article let us look into the way of displaying sequential numbers in Yukon (code name of Sql Server 2005).

Method 1 (Sql Server 2000)

Create table TestTable
(
EmployeeNumber int,
FirstName varchar(50),
Salary money NULL
)
Go

Insert into TestTable Values (100,'Vadivel',10000)
Insert into TestTable Values (200,'Vadi',20000)
Insert into TestTable Values (300,'vel',30000)
Go

Select Identity(int, 1,1) as 'Serial number', * INTO #TempTable from TestTable
Select * from #TempTabledrop table #TempTable

Method 2 (Sql Server 2005)

Row_Number() :: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Select EmployeeNumber, FirstName, Row_Number() Over (Order By EmployeeNumber) as 'Row Number' From TestTable

No comments: