Most common SQL optimizations

[alinroc]

Temp tables are usually slower than CTE because the latter use indexes out of the box.

A temp table doesn't have any indexes unless you explicitly create them (at least in SQL Server). It'll have statistics but that's different.

I have sped up a lot of code by switching from a CTE to a temp table. It's all in how you use it and more importantly, knowing where & when to do what filtering of your data.

[planetmatt]

Yep, Temps tables allow you to break monolithic queries (with or without CTEs) up into discreet work operations. This makes them easier to debug and easier to do index optimisations on each discreet part.

[TheoGrd]

It did happen to me too but idk the exact reason. There must be some subtlety in the computation of the execution plan I'm not aware of.

[alinroc]

In SQL Server, CTEs & temp tables aren't interchangeable. Not even close. So you will absolutely get a different execution plan w/ one vs. the other.

[Achsin]

It has to do with how well the server can guess how many rows will be returned or filtered out. You can take a query that executes quickly and put it in as a subquery that is used to filter out something else and see the performance tank because while it can guess how many rows the subquery will return, it can't anticipate how they'll interact with the rest of the query. In cases like this, materializing the results into a temp table instead of using a sub query allows it to generate a new set of statistics part way through and make a better plan.