SQL SERVER SELECT statement with auto generate row id

Question

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

User · Answer

Select  Select count y au lname  from dbo authors y where y au lname   y au fname  lt   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

User · Answer

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

User · Answer

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

User · Answer

Select  Select count y au lname  from dbo authors y where y au lname   y au fname  lt   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

User · Answer

If you are making use of GUIDs this should be nice and easy  if you are looking for an integer ID  you will have to wait for another answer   SELECT newId   AS ColId  Col1  Col2  Col3 FROM table1   The newId   will generate a new GUID for you that you can use as your automatically generated id column

User · Answer

This will work in SQL Server 2008   select  top 100 ROW NUMBER   OVER  ORDER BY tmp FirstName     from tmp   Cheers

User · Answer

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

User · Answer

Is this perhaps what you are looking for      select NEWID     from TABLE

User · Answer

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

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

Examples related to sql-server