I started out in Hugo's camp. I'm always glad to have learned a new function, but one thing I can't get away from is that, in the real world, if I'm creating a unique row identifier, then I'm probably going to come back and look for it later and for that I will almost always want an index. So that makes it a choice between the two alternatives below. It looks like six of one and a half dozen of the other.

Before stopping to think about which was "better", I would have called it a draw. (And being superstitious, I still am in the habit of avoiding SELECT... INTO....)

However, populating a table before building the index means that using SELECT...INTO with the IDENTITY function (followed by a CREATE INDEX) is going to run FASTER than creating a table with a primary key predefined and then doing an INSERT INTO ... SELECT. (A couple of scripts follow at the bottom if anyone wants to test the difference at a million rows.)

This isn't going to make me go back and change all of our existing stored procedures, but it is something to be remembered going forward.

---------------------------------------------

-- first thought: who cares?

---------------------------------------------

create table #temp(ID int identity(1,1) PRIMARY KEY, foo1 varchar (20), foo2 int)

insert into #temp

select foo1, foo2

from SomeOtherTable

-- vs --

SELECT IDENTITY(int, 1,1) AS ID_Num, foo1, foo2

into #temp

from SomeOtherTable

PLUS

ALTER TABLE .........

or

CREATE INDEX

-----------------------------------------

-- test scripts

-----------------------------------------

-- using IDENTITY function and creating index afterwards

SELECT IDENTITY(int, 1,1) as ID , getdate() + N as Futureday

into #future

from tally

create unique clustered index PK_Future on #future (ID)

select count(*), max(Futureday) from #future

drop table #future

go

-- predefining table with an IDENTITY column

create table #future (ID int identity(1,1) primary key, Futureday datetime)

insert into #future

select getdate() + N as Futureday

from tally

select count(*), max(Futureday) from #future

drop table #future

go