Best use of indices on temporary tables in T-SQL

Question

If you're creating a temporary table within a stored procedure and want to add an index or two on it, to improve the performance of any additional statements made against it, what is the best approach? Sybase says this:

"the table must contain data when the index is created. If you create the temporary table and create the index on an empty table, Adaptive Server does not create column statistics such as histograms and densities. If you insert data rows after creating the index, the optimizer has incomplete statistics."

but recently a colleague mentioned that if I create the temp table and indices in a different stored procedure to the one which actually uses the temporary table, then Adaptive Server optimiser will be able to make use of them.

On the whole, I'm not a big fan of wrapper procedures that add little value, so I've not actually got around to testing this, but I thought I'd put the question out there, to see if anyone had any other approaches or advice?

Answer 1

A few thoughts:

• If your temporary table is so big that you have to index it, then is there a better way to solve the problem?

• You can force it to use the index (if you are sure that the index is the correct way to access the table) by giving an optimiser hint, of the form:

  SELECT * 
  FROM   #table (index idIndex) 
  WHERE  id = @id
  

If you are interested in performance tips in general, I've answered a couple of other questions about that at some length here:

• Favourite performance tuning tricks
• How do you optimize tables for specific queries?