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