As with almost everything in SQL Server, there is no one-size-fits-all answer when it comes to temp tables. There are times where temp tables have rescued a query, and others where they’ve just caused trouble. And then there’s the times where you’re just scratching your head.
Can you use a CTE or a derived table in place of a temp table? In many cases, yes! But it doesn’t always work out well. When things get particularly complex, the SQL Server optimizer can struggle to find a good plan that doesn’t involve lots of table scans.
Read on for Andy’s thoughts.