What is a table expression? Table expressions are named query expressions that represent a valid relational table. Microsoft SQL Server supports four types of table expressions: derived tables, common table expressions (CTEs), views, and inline table-valued functions (inline TVFs). Table expressions are not physically materialized anywhere; they are virtual and therefore only exist very briefly in computer memory. A query against a table expression is internally translated to a query against the underlying objects. The benefits of using table expressions are typically related to logical aspects of your code and not to performance.
A query against a table expression involves three parts in the code:
- the inner query
- the name that you assign to the table expression
- the outer query
Inner Query Requirements
The inner query is supposed to generate a table result, which means it needs to satisfy three requirements. The first requirement refers to the ORDER BY clause. The inner query cannot have a presentation ORDER BY clause. What does that mean? It can have an ORDER BY clause to support a TOP or OFFSET-FETCH filter, but the outer query doesn’t give you assurance that the rows will be presented in any particular order, unless it has its own ORDER BY clause. The code below produces the error below that.
SELECT * FROM ( SELECT [custid], [city] FROM [Customers] ORDER BY [Custid] ) AS Cust
Msg 1033, Level 15, State 1, Line 7 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
The second requirement is that all columns must have names, so if you have a column that is the result os a computation you must use an alias.
All column names must be unique. This is the third requirement.
A derived table closely resembles a subquery. It is a table subquery that is defined in the FROM clause of the outer query. All columns of a table expression must have names, and therefore you must assign column aliases to all columns that are the results of computations.
Below is a very simple example of a derived table. Note that a derived table is not needed because the outer query doesn’t apply any manipulation.
SELECT * FROM ( SELECT [custid], [city] FROM [Customers] ) AS Cust
We can nest one within another. The following sql code produces the same results as the above code.
SELECT * FROM ( SELECT * FROM ( SELECT [custid], [city] FROM [Customers] ) AS Cust ) AS CutomerList