Generate ROWNUM in Microsoft SQL Server
At first create a temporary Table with some sample data as shown below
create table users( name varchar(100) )
insert into users values('Stefan')
insert into users values('George')
insert into users values('John')
--ROWNUM using ROW_NUMBER
SELECT ROW_NUMBER () OVER (ORDER BY name) AS row_num, name FROM users ORDER BY name ASC
--ROWNUM using IDENTITY
if(exists(select COUNT(*) from sysobjects where name = '#tmp' )) drop table #tmp
SELECT IDENTITY(int, 1 , 1) AS row_num , name
INTO #tmp
FROM users ORDER BY name ASC
SELECT row_num , name FROM #tmp
--ROWNUM using NEWID
SELECT NEWID() AS row_num, name FROM users ORDER BY name ASC
Comments
Write the first comment
Leave a trace
Post being viewed right now
