Table Value Constructors in TSQL

Last week’s quiz was on 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?

SELECT *
FROM (VALUES 
   ('Spider Plant', 'Chlorophytum comosum', 'airplane plant'),
   ('Aloe Vera', 'Aloe vulgari', 'Burn Plant')
)as v(name1, name2, name3);
  • 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.

Screenshot of the results from question 1

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);
  • 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.

Screenshot of the results of question 2

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);
  • 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.

Screenshot of the results of question 3

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);
  • 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.

Screenshot of the results from question 4

 

CROSS APPLY in T-SQL: Why to APPLY

Why use APPLY?

Michelle asked a great question on the quiz page:

In your own words, why would one want to use a cross apply operator rather than a join operator? I’m old school, and I’m just not getting why a cross apply would be so much better to use than a join.

Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY:

  1. APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to be about thinking through how apply works — but it’s still the #1 use.
  2. Another thing I might use it for is when a query needs a correlated subquery — somewhat like an inline function.
  3. And I also like it for queries that have a calculation that needs to be done and which is referenced in multiple columns in the query, or perhaps also in a predicate and the select. You can perform the computation once in the apply and then reference it multiple times. That way if you have to change the formula later on, you only have to change it in once place, plus I find it’s easier to read in some cases.

But it can do even more…

It can help you (un) pivot data. Check out Kenneth Fisher’s example here.

Brad Schulz’s post here has code samples for those, plus more things like showing how it can be useful for shredding XML: http://bradsruminations.blogspot.com/2011/04/t-sql-tuesday-017-it-slices-it-dices-it.html

Brad gets a bit jokey at the end of the post, but there’s a lot of valid uses along the way.

Want to take the quiz on APPLY?

Take the quiz here, then head back here to see how you compare to other folks who took the quiz– reported below.


Quiz Results/Answers/Explanations: CROSS APPLY

The questions in this quiz are based on two tables, created and populated with the following commands:

CREATE TABLE dbo.t1 (t1c1 int IDENTITY PRIMARY KEY); 
CREATE TABLE dbo.t2 (t2c1 int IDENTITY PRIMARY KEY, t1c1 int NOT NULL DEFAULT 'foo'); 
GO

INSERT dbo.t1 DEFAULT VALUES
GO 5
INSERT dbo.t2 (t1c1) SELECT TOP (2) t1c1 FROM dbo.t1;
GO

This produces two tables which look like this:

Question 1: If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

SELECT COUNT(*) AS ct 
FROM dbo.t1
CROSS APPLY (SELECT 1 / 0 AS x 
     FROM dbo.t2 
     WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: The results of COUNT(*) based on an inner join between the two tables

  • Correct: 106 (42%)
  • Incorrect: 146 (58%)

I know, it’s weird, there’s a 1/0 in there which seems like it would produce a divide by zero error! But that bit of code never gets evaluated in this case, and the query runs successfully.

Sometimes a query using APPLY can be “transformed” into a join operation, and this is one of those cases. You can read more about this in Paul White’s article here.

Most of the time, if you want a join, you should use a join. However, it’s definitely useful to know that APPLY can be used this way for when you are reading and editing code written by others.

Question 2: Will these queries return the same result?

SELECT COUNT(*) AS ct
FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

SELECT COUNT(*) AS ct
FROM dbo.t1
CROSS APPLY (SELECT 1 / 0 AS x 
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: yes

  • Correct: 140 (56%)
  • Incorrect: 112 (44%)

In this case, the second query will be rewritten to use an inner join, just like the first query. One method you can use to verify this is to look at the execution plan for both queries (and you can obviously run them and compare the results).

Both queries return a count of 2.

Here are the estimated plans for both queries:

Question 3: Will these queries return the same result?

SELECT COUNT(*) AS ct
FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

SELECT COUNT(*) AS ct
FROM dbo.t1
OUTER APPLY (SELECT 1 / 0 AS x 
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: no

  • Correct: 198 (79%)
  • Incorrect: 54 (21%)

The first query returns the count of 2. The second query returns a count of 5.

This is because the first query is implemented as an inner join, and the second query is implemented as an outer join.

Question 4: Will these queries return the same result?

SELECT t1c1 * 2 AS d
FROM dbo.t2;

SELECT x.d
FROM dbo.t2
CROSS APPLY (SELECT t1c1 * 2 AS d) AS x;

Answer: yes

  • Correct: 161 (64%)
  • Incorrect: 91 (36%)

This is an example of using a CROSS APPLY to compute a calculation.

It’s pretty trivial in this case, but I find this can be very useful in the case of complex queries where you refer to the result of a calculation more than once — doing this can allow you to only do the computation in once place and then refer to it many times. Not only can this make the query more readable sometimes, it can limit the errors if you need to change the calculations (because you’re changing it in fewer places.)

 

A New Look and Feel for the SQLWorkbooks Quiz Archive

Ch-ch-ch-ch-changes

I’ve revamped the section of the site where I archive the quizzes I make for the Weekly Quizletter. The biggest changes are:

  • I got rid of the old menu, which just didn’t scale to the amount of quizzes we have now
  • I broke the quizzes into topics. Each topic has its own page, listing the quizzes under that topic
  • I made a deeply meaningful image header for each quiz topic page

Check out the revamped quizzes section here – and take a quiz or two, why not?

I plan to do a little more fine tuning once I see if the new setup works for folks or not.

If you have feedback, I’d love to hear it.