T-SQL: Derived tables & table value constructors

In this technical blogpost one of our developers, Peter de Heer, highlights T-SQL derived tables & table value constructors. He then presents a well performing technique for doing unpivoting operations with them.

Derived tables are intermediate query results generated in a from clause, useful for:

• filtering and/or grouping of data, before other query operations (such a joins)
• injecting a table of query-specific constants using table value constructors

In T-SQL, derived tables have the following syntax:

The mandatory table alias is to make the result usable by other parts in the query.

Table value constructors use the values clause must be used inside a derived table or an apply, like this:

Executing this on SQL Server results in:

This technique for producing table values is limited to 1000 rows and lacks column-names. These missing column-names must be specified using the table alias as specified in: as DaysOfWeek (DayIndex, DayName)

The expressed column-values can also be non-constant! And to refer to prior data produced in the query, the values clause must be used inside an apply and not a derived table.

Executing this on SQL Server results in:

Please notice:

• Day 6 and 7 get a shorter DayName when 1 is specified in a table produced earlier
• An Apply is only needed when column-value expressions refer data produced earlier in the query

While diving deep into apply is outside the scope of this blog post, this practical use case is worth presenting here, please enjoy this example and till next time!

This code converts this input:

Into this: