Tell me more ×
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
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
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.