I probably overuse temp tables. I’m the first one to admit it. But often when I choose to use one, it’s a choice between:
- Spending a long time trying to business understand logic for a company I don’t work for
- Tinkering with query syntax and using really weird tricks and hints to get different plan shapes
- Waiting a long time to create or tweak indexes on huge tables (usually on “dev” hardware)
Erik provides a good example of where a temp table fits well, contrasting it to a common table expression that fares poorly. My bias tends to come in the opposite direction from Erik’s: I’m susceptible to playing query golf and getting everything I need back in one optimized call. But part of craftsmanship is knowing how and when to use each tool.