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?