Steve Jones shares some thoughts on temporary tables:
My answer to Jeff’s question of temp tables as friend or foe is yes.
They are friends.
They are foes.
Most things that we struggle with in database work are tradeoffs. We have to balance the demands. It’s why we say “it depends” so often because we have to find a way to do more of one thing, while accepting less of another.
This reminds me of a line I heard from a computer science professor I had in school. His line was that computer engineers get miracles every other day—they get some new breakthrough in hardware that makes things faster, cheaper, smaller, etc. But in computer science, we only ever got one miracle: when you want to access a piece of information, the most likely next piece of information you want is near to the piece you accessed. That’s why we can work with memory in pages and how caching can be successful. Everything else in computer science is a trade-off.