In this post we will discuss about table expressions. Table expressions are named queries and they are not physically materialized anywhere—they are virtual.
T-SQL Support four forms of table expressions:
– Common Table Expressions (CTEs)
– Inline table-valued functions
Derived tables and Common Table Expressions are visible only to the statement that defines them. Views and inline table-valued functions preserve the definition in the database as an object.
You can reuse views and inline table-valued functions and you can also control access to the object with permissions.
Some rules you have to follow when you are going to use table expressions:
– All columns returned by the inner query must have names, and all column names must be unique.
– The inner query is not allowed to have an ORDER BY clause.
– If the inner query uses the TOP or OFFSET-FETCH option, it’s allowed to have an ORDER BY clause as well. And then the outer query has no presentation ordering guarantees if it doesn’t have its own ORDER BY clause.
When SQL Server optimizes queries involving table expressions, it first unnests, or inlines, the table expression’s logic, and therefore interacts with the underlying tables directly.
It does not persist the table expression’s result, so if you want to persist the result for further processing, you should use a temporary tables or table variables.