WITH vs Insert/Select Into #Temp

I recently learned about the WITH clause. On projects in the past, we’ve typically used temporary tables to create sub-tables from which information can be queried into more complex queries.

Are there any performance differences/considerations between WITH and temp tables? What other considerations should I have when using either?

Thanks!

I think the biggest difference is that common table expression using WITH are only really designed to last as long as the query does. So if you’re need of a temporary table for a single query then I think WITH is a much better choice.

If you want a table that would then be accessed multiple times in a single session than an actual temporary table would probably be the better choice.

Exact performance differences might be tricky to pin down (may need proper testing) but I’d assume CTEs would be more performant for single queries (subqueries vs. CTEs though is a much tougher comparison :slightly_smiling_face:). Have a wee web search for better information if the performance difference is actually important to your work.

1 Like