It is often required to create unique values for every row in a result set query in SQL Server. But SQL Server doesn’t have any built in functions such as ROWNUM in oracle to support this feature. There are many ways in which we can generate unique numbers in the select query as shown in the below scenario
Consider the Employee table with EmployeeName and EmployeeID columns and the scenario is to select records from the table based on EmployeeID with uniqueid for each row in the result set. The select statement can be written in below ways
Create table Employee ( EmployeeID INT , EmployeeName Varchar(30))
ROWNUM using ROW_NUMBER
SELECT ROW_NUMBER () OVER (ORDER BY EmployeeID) AS RowNumber, EmployeeName
FROM Employee ORDER BY EmployeeID
ROWNUM using IDENTITY
SELECT IDENTITY(int, 100, 1) AS RowNumber , EmployeeName
INTO #tmp
FROM Employee ORDER BY EmployeeID
SELET RowNumber , EmployeeID FROM #tmp
ROWNUM using NEWID
SELECT NEWID() ,AS RowNumber, EmployeeName FROM Employee
ORDER BY EmployeeID
Related article:
http://samsudeenb.blogspot.com/2009/06/how-to-generate-sequence-number-in-sql.html
