Temporary Tables, Table Variables, Table Expressions - Part 3/4

Table Expressions
In addition to physical temp tables we also have logical temporary tables which are merely virtual materialization of interim sets (opposed to physical materialization in temporary tables and table variables). Table expressions which include derived tables, common table expressions (CTEs), views and inline table-values UDFs give you this capability. This article will discuss situations where you may or maynot want to utilize these table expressions.

In general, table expressions should be used in cases when you need the temporary objectly for simplification. Table expressions should also be used when you only need to access the temporary data once or a limited number of times and do not need to index the interim results. When you actually run a query with a table expression, the query optimizer actually merges the underlying query with the outer one, querying the underlying tables directly.

Other than simplification, there will be some cases where you'll want to table expressions in order to improve performance -in these cases the optimizer might generate a better plan for your query compared to other alternatives.

In terms of scope and visibility, derived tables and CTEs are available only to the current statement, while views and inline UDFs are available globally.

Temporary Tables (#Tables)
Table Variables (@Tables)
Table Expressions (CTEs)