Rob Farley explains a position:
Let me say for starters that I’m pleased we have temporary tables. They’re tremendously useful, but often get abused. Compared to obvious villains like NOLOCK, cursors, and scalar functions, they’re really quite inane (but any of these features can be used for good – trust me). But temporary tables were the one of these I mentioned in last month’s post about signs of a bad query. If nothing else, using temporary tables probably means you’re writing procedural code rather than set-based queries.
The scenario I want to talk about is using temporary tables to materialise a set of data, ahead of using it in a separate query a moment later. That’s essentially what Jeff Taylor is asking us to comment on.
I agree very strongly with Rob’s position. In stored procedures, temp tables should be there for a specific reason, a resignation that it is a second- or third-best design decision. But sometimes, you need second-best because first-best doesn’t perform well enough.