SQL SERVER, SELECT statement with auto generate row id

Question

Does anyone happen to remember what is the the function of select state to grab records from sql server with additional row id column automatically generate?

Edit: I am happen to work again sql 2000

Answer 1

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.

Answer 2

Is this perhaps what you are looking for?

select NEWID() * from TABLE

Answer 3

INDENTITY(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.

Answer 4

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

Answer 5

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/

---

Answer 6

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