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
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
Comments