Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Related article from SQLTeam.com?

share|improve this question

closed as off-topic by Mark Storey-Smith, dezso, Mat, StanleyJohns, Kin Aug 5 '13 at 18:53

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, dezso, Mat, StanleyJohns, Kin
If this question can be reworded to fit the rules in the help center, please edit the question.

    
Please show the execution plans. –  Martin Smith Jun 30 '12 at 10:04
    
What are the differences in page counts for the two tables? What query are you running against these tables that is 3x slower, or are you saying that populating the table and building the index itself is what is slower? –  Aaron Bertrand Jun 30 '12 at 13:30
    
Did the other table fall out of cache (use STATISTICS IO to find out)? –  Jon Seigel Jul 1 '12 at 13:56
    
I feel that the first version is more maintainable since you have the explicit field types. Harder to produce strange bugs that way. –  dezso Jul 2 '12 at 16:44
    
I am unable to reproduce this issue. In fact I now get the faster runtimes with no key at all so perhaps statistics are to blame. –  crokusek Jul 5 '12 at 0:34