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.