[sql-server] SQL SERVER, SELECT statement with auto generate row id

Does anyone happen to remember the function name used to generate sequential row number built-in SQL Server 2000.

Here is a simple method which ranks the rows after however they are ordered, i.e. inserted in your table. In your SELECT statement simply add the field

ROW_NUMBER() OVER (ORDER BY CAST(GETDATE() AS TIMESTAMP)) AS RowNumber.

Select (Select count(y.au_lname) from dbo.authors y
where y.au_lname + y.au_fname <= x.au_lname + y.au_fname) as Counterid,
x.au_lname,x.au_fname from authors x group by au_lname,au_fname
order by Counterid --Alternatively that can be done which is equivalent as above..

Do you want an incrementing integer column returned with your recordset? If so: -

--Check for existance  
if  exists (select * from dbo.sysobjects where [id] = object_id(N'dbo.t') AND objectproperty(id, N'IsUserTable') = 1)  
drop table dbo.t
go

--create dummy table and insert data  
create table dbo.t(x char(1) not null primary key, y char(1) not null)  
go  
set nocount on  
insert dbo.t (x,y) values ('A','B')  
insert dbo.t (x,y) values ('C','D')  
insert dbo.t (x,y) values ('E','F')

--create temp table to add an identity column  
create table dbo.#TempWithIdentity(i int not null identity(1,1) primary key,x char(1) not null unique,y char(1) not null)  

--populate the temporary table  
insert into dbo.#TempWithIdentity(x,y) select x,y from dbo.t

--return the data  
select i,x,y from dbo.#TempWithIdentity

--clean up  
drop table dbo.#TempWithIdentity

Select (Select count(y.au_lname) from dbo.authors y
where y.au_lname + y.au_fname <= x.au_lname + y.au_fname) as Counterid,
x.au_lname,x.au_fname from authors x group by au_lname,au_fname
order by Counterid --Alternatively that can be done which is equivalent as above..

You can do this directly in SQL2000, as per Microsoft's page: http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

 select rank=count(*), a1.au_lname, a1.au_fname
   from authors a1, authors a2
   where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
   group by a1.au_lname, a1.au_fname
   order by rank

The only problem with this approach is that (As Jeff says on SQL Server Central) it's a triangular join. So, if you have ten records this will be quick, if you have a thousand records it will be slow, and with a million records it may never complete!

See here for a better explanation of triangular joins: http://www.sqlservercentral.com/articles/T-SQL/61539/

Is this perhaps what you are looking for?

select NEWID() * from TABLE

IDENTITY(int, 1, 1) should do it if you are doing a select into. In SQL 2000, I use to just put the results in a temp table and query that afterwords.

This will work in SQL Server 2008.

select  top 100 ROW_NUMBER() OVER (ORDER BY tmp.FirstName) ,* from tmp

Cheers