Table Value Constructors in TSQL

Last week’s quiz was on Table Value Constructors in TSQL.

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?

SELECT *
FROM (VALUES 
   ('Spider Plant', 'Chlorophytum comosum', 'airplane plant'),
   ('Aloe Vera', 'Aloe vulgari', 'Burn Plant')
)as v(name1, name2, name3);
GO

• Answer: 2
• Correct: 252 (86%) / Incorrect: 40 (14%)

The folks who got this incorrect mostly went for 3 – if you haven’t used these before, it’s unclear how those commas work!

Table value constructors allow you to specify multiple rows, each surrounded by (round parens). Look for those round parens to identify the rows.

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

SELECT * 
FROM ( VALUES 
   ('Heart Leaf Philodendron'),
   ('Philodendron cordatum')
) as v(name1)
CROSS APPLY ( VALUES 
   ('green'),
   ('leafy')
) as v2(name1);
GO

• Answer: 4
• Correct: 239 (82%) / Incorrect: 53 (18%)

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.

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

SELECT * 
FROM (VALUES 
   ('Succulents'), 
   ('Cacti') 
) as v(thing) 
CROSS APPLY (VALUES 
   (v.thing + ' are friends')
) as v2(thing);
GO

• Answer: 2
• Correct: 220 (75%) / Incorrect: 72 (25%)

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.

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

SELECT v.plant, v2.faves 
FROM (VALUES 
   ('Ferns', 'being spritzed', 'shade', 'humidity'), 
   ('Succulents', 'well drained soil', 'to dry out a bit', 'brighter light')
) as v(plant, fav1, fav2, fav3)
CROSS APPLY ( VALUES 
   (fav1),
   (fav2),
   (fav3)
) as v2(faves);
GO

• Answer: 6
• Correct: 202 (69%) / Incorrect: 90 (31%)

I find this to be a very interesting pattern: we are using a table value constructor to do an unpivot operation!

The first table value constructor (v), is a two row table with four columns: plant, fav1, fav2, and fav3.

The second table value constructor (v2), does something that seems almost impossible:

• It refers to the fav1, fav2, and fav3 columns from (v)
• It places all THREE of these columns into a single column named (faves)

This is allowed, and it does work.

The number of rows we get back is the number of values in fav1, fav2, and fav3 - there are two rows and three columns, so 2 x 3 = 6 rows.