Table Value Constructors in TSQL

Essentially, table value constructors let you create a dataset on the fly. These can occasionally be useful in writing queries, but I think playing with them has another benefit: they provide a simple, lightweight framework to let you develop your ability to think in sets.

I wrote this quiz building up to question #4, which I think is a very fun and interesting pattern.

Let’s take a look at how folks did on the quiz, and see what table valued constructors can do.

Quiz Answers and Explanations

Question 1: A single Table Valued Constructor! How many rows will this query return?

Question 2: I’ve cross applied my TVC with another TVC. How many rows will this query return?

We have two table value constructors, and each of them has one column and two rows.

If you look at the execution plan for this query, it takes the datasets and joins them with an INNER JOIN with no join predicate – in this case it’s as if we did a cross join, and got the cartesian product. Each dataset has two rows, so 2 x 2 = 4.

Screenshot of the results of question 2

Question 3: Something changed in that APPLY. How many rows will this query return?

Whoo, the second table value constructor (v2) is doing something interesting this time: it’s referring to the dataset produced by the first table value constructor (v1), and concatenating on a literal value to the ‘thing’ column.

The results here have two rows: the second TV creates an additional column.

Note: you could get the exact same results using a SELECT to define v2 instead of a VALUES clause.

Screenshot of the results of question 3

Question 4: Hmmmm… the CROSS APPLY got even more interesting. How many rows will this return?