Press "Enter" to skip to content

When to Index Temp Tables

Erik Darling thinks about what phase in a procedure one should index a temp table:

You already know that your temp table needs an index. Let’s say there’s some query plan ouchie from not adding one. You’ve already realized that you should probably use a clustered index rather than a nonclustered index. Adding a nonclustered index leaves you with a heap and an index, and there are a lot of times when nonclustered indexes won’t be used because they don’t cover the query columns enough.

Good. We’ve fixed you.

But, like, when should you create the index?

I try to do as many inline operations as I can with temp tables because doing so means you might be able to take advantage of temp table reuse, and on a frequently-running procedure, that can make a difference.