I worked on a complex query the other days. I tried to use derived tables first, and realized derived table wouldn’t work because it cannot be referenced in nested sub-queries. Then I ran through a few ideas such as Common Table Expression (CTE), table variable, temp table, etc, before I chose CTE to finish the query.

I analyzed my decision process, and believed that the scope was the most important factor, and followed by performance and disk cost.

Object Type Execution Scope Comment
Derived Table Current executing statement Cannot be referenced in nested sub-queries
CTE Current executing statement Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Table Variable Current executing batch
Temp Table (local) Current database session

Please note that all objects mentioned above are temporary in-memory or database structures, and differ in areas such as scope, performance, disk cost, etc. I only show the scopes of those objects in this blog.