I have a stored procedure that looks something like this:

SELECT columnlist
INTO #temptable
FROM Table
JOIN lotsofothertables
WHERE severalconditions

UPDATE anothertable
SET column = #temptable.column
FROM anothertable 
JOIN #temptable
    ON anothertable.PKColumn = #temptable.PKColumn

The initial query will frequently produce just a few columns (<100) so the update is fast. But occasionally (and these are the runs we are having problems with) it produces 1000's or even 10,000's. Would it be reasonable to add something like this between them?

IF @@RowCount > 100
   CREATE INDEX ix_temp ON #temptable(PKColumn)

Or am I better off just creating the index on the table regardless since with so few rows the amount of time spent creating the index will be fairly small?

  • Is PKColumn the PK of the temp table? I.e. It's unique? – Martin Smith Apr 29 '16 at 20:55
  • Lets assume that it is. – Kenneth Fisher Apr 29 '16 at 20:56
  • 3
    I would just always create the index (but inline as Martin described). – Aaron Bertrand Apr 29 '16 at 22:04

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes, column names, and nullability can then be seen much more explicitly (and both can be minimally logged).

In this case if you were to create the temp table with an unnamed primary key constraint on the column up front

CREATE TABLE #temptable

rather than creating the table and adding an index afterwards you would be able to benefit (at least potentially as that does have some issues) from temp table object caching.

I wouldn't bother making it conditional on row count unless you've proved that the presence of the index with few rows somehow makes a significant deterioration in performance (unlikely).

  • 3
    Personally, I've never found putting a PK/index on a temp table to be a noticeable, detrimental difference when the table was small. However, it always helps when the table ends up being bigger. – LowlyDBA - John M Apr 29 '16 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.