When testing stored procedures in SSMS, it sometimes says there is a missing index on #someTempTable____________________000000000000005B] (someField) etc etc

When I do add them like this to the sp:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[#someTable] ([someField])

I never seem to see a speed improvement. So, I often do not add such indexes. When should I be adding such indexes?

4 Answers 4


Add an index if you'll use your temp table and its index twice or more during the query run.

Or to maintain usual index tasks, like uniqueness

If your data loaded into temp table are already sorted, the to create temp table with the same clustered index as sort of data


taking into account sql server's feature of temp tables reuse- if you decide to create an index on temp table - try to do it in CREATE TABLE statement. If you'll add an index explicitly after table creation - it will prevent sql server to reuse that table next time

  • You can't add an index in the CREATE TABLE statement. Only constrains can be added there. Aug 21, 2014 at 10:32
  • @ErikDekker Creating some constraints means creating indices also
    – Oleg Dok
    Aug 22, 2014 at 5:30
  • I know. But the question was about adding a nonclustered index. That can't be done directly in the create statement ;-) Aug 22, 2014 at 9:38
  • @ErikDekker You can specify which type of index will be created for constraint
    – Oleg Dok
    Nov 22, 2014 at 19:27

This is hard to answer without a lot more information. Generally, I would say, "it depends".

Generally speaking I add indexes onto a temp tables if the benefit of the index is greater than the original execution cost plus the cost of creating the index. You would have to do a benchmark using SET STATISTICS IO as an example. I would also want to review the execution plans used before and after the index is added. Sometimes recommended indexes are not really good recommended indexes :-)

  • What kind of information would you suggest I add to my question to make it more helpful?
    – O.O
    Jan 19, 2012 at 20:58
  • An example of your temp table usage would be helpful. How often is it executed? How often would you read from the local temp table? I would also like to see the logical reads, writes, the execution plans for the code. If you had a baseline of the before and after this would let you know if the index is really helpful.
    – JohnS
    Jan 19, 2012 at 21:06
  • I'm not sure how to do all of that, but I can start with posting the actual explain plan of the stored procedure that uses the non-indexed temp tables, if you think that would be helpful?
    – O.O
    Jan 19, 2012 at 21:08
  • Sure, lets go for it. Here is also an link to show you how SET STATISTICS works. Can you run this against a development box? You would want to evaluate both senarios with and without the new index. You would also want to pull the execution plan for both executions.
    – JohnS
    Jan 19, 2012 at 21:20
  • Understood. How should I paste the explain plan? As XML?
    – O.O
    Jan 19, 2012 at 21:43

Given the fact that the temp table you indicated has local scope (as denoted by the single #), it is generally not best to add an index as the table will only alive for the duration of the connection.

  • Even if it's used multiple times in, let's say, a 2000+ line stored procedure?
    – O.O
    Jan 19, 2012 at 20:20
  • Sure - I've used them before in stored procs of that size with relative ease. Provided there are no query hints, the optimizer should be able to compensate. Jan 19, 2012 at 20:23

you should update statistics after a significantly large change in the table, for example: create tempTable, add index, insert 10k records... UPDATE STATISTICS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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