Conditionally add an index to a temp table

Question

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?

Answer 1

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
(
PKColumn INT PRIMARY KEY
)

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