When I create a temporary table with a PK defined from the outset like this I get great performance in subsequent joins on that key:
create table #temp (
field1 int not null
field2 int not null,
primary key(field1, field2) -- works great, optimizer uses it
);
-- Populate the table with data
-- Use table in join and note time.
However, if I create the table without the primary key and add an unique clustered index after populating the data, it appears to be ignored (3x slower).
create table #temp (
field1 int not null
field2 int not null,
);
-- Populate the table with data
-- Create index very similar to PK
create unique clustered index IX_UniqName on #temp(field1, field2);
-- use table in join and note much longer run time (3x for me)
The reason I want to be able to add the index later is because I really want to use the implicit format to create the temp table since it eliminates the create table statement entirely resulting in reduced lines of code to maintain and a cleaner looking procedure.
-- Assume the table has 30 columns and format 1 feels like a cruel joke
-- Implicitly create the table!
select [1],[2],...[30]
into #temp
from X;
create clustered index ...
** I don't care about the physical rebuild of the table. I am primarily interested in maintainability for purpose of the question. Anyway, I also tested with a non-clustered index with similar results (no improvement). There are no-nulls in the key. Tested using SQL Server 2008 Express.
STATISTICS IOto find out)? – Jon Seigel Jul 1 '12 at 13:56