SQL Server Table Expressions

 in SQL Server  tagged table / expression / derived table by

What is a table expression? Table expressions are named query expressions that represent a valid relational table. 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.

Microsoft SQL Server supports four types of table expressions:

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.