Common Table Expression (CTE) benefits?

From msdn :


Unlike a derived table, a CTE can be self-referencing and can be
referenced multiple times in the same query.

I'm using CTEs quite a lot, but I've never thought deeply about the benefits of using them.

If I reference a CTE multiple times in the same query:

• Is there any performance benefit?

• If I'm doing a self join, will SQL Server scan the target tables twice?

As a rule, a CTE will NEVER improve performance.

A CTE is essentially a disposable view. There are no additional statistics stored, no indexes, etc. It functions as a shorthand for a subquery.

In my opinion they can be EASILY overused (I see a lot of overuse in code in my job). Some good answers are here, but if you need to refer to something more than once, or it's more than a few hundred thousand rows, put it into a #temp table instead and index it.

StackExchange Database Administrators Q#14490, answer score: 27