The Table Value Constructor (aka the VALUES keyword)
June 19, 2021
In this post, we'll look at a helpful but lesser-known feature of T-SQL, the Table Value Constructor.
You've probably seen it used as part of an
INSERT Statement, where it is easily identifiable as the
VALUES keyword isn't exclusive to an
INSERT Statement and can be used in other ways that we'll explore below.
The TVC is an expression that allows you to define a table row by row (known as row expressions) using the keyword
The Table Value Constructor has the following layout:
VALUES (value_1, value_2, value_3, etc) ,(value_1, value_2, value_3, etc) ,(value_1, value_2, value_3, etc) ...etc
The TVC starts with the keyword
VALUES, followed by a list of rows that form the returned table.
Each row and its column values go inside parentheses
(), with a comma separating the column values. Each additional row follows with a comma and parentheses.
So how do we use the Table Value Constructor? In a couple of ways.
Here is where you'll commonly see the Table Value Constructor. In a Multi-row Insert, the Table Value Constructor defines a table that gets inserted as a whole rather than row by row.
DROP TABLE IF EXISTS #hundred_meter_records CREATE TABLE #hundred_meter_records ( duration FLOAT ,set_by VARCHAR(30) ,set_on DATE ,place VARCHAR(50) ) INSERT INTO #hundred_meter_records (duration, set_by, set_on, place) VALUES(9.58, 'Usain Bolt', '2009-08-16', 'Berlin, Germany'),(9.69, 'Tyson Gay', '2009-09-09', 'Shanghai, China'),(9.69, 'Yohan Blake', '2012-08-23', 'Lausanne, Switzerland'),(9.72, 'Asafa Powell', '2008-10-02', 'Lausanne, Switzerland'),(9.74, 'Justin Gatlin', '2015-05-15', 'Doha, Qatar') SELECT * FROM #hundred_meter_records
An interesting feature of the TVC is that it also functions as a derived table.
To do this, we wrap the TVC in parenthesis
() and include it in the
FROM clause of a query. As you'd expect, it is then available to use in the
SELECT olympiad ,host_city FROM ( VALUES (2012, 'London') ,(2016, 'Rio de Janeiro') ,(2020, 'Tokyo') ,(2024, 'Paris') ,(2028, 'Los Angeles') ) as olympics(olympiad, host_city)
One thing to point out with the TVC, it's not possible to name columns as part of the TVC call. So instead, we alias the returned table and assign column names inside the parentheses which follow.
... ) as olympics(olympiad, host_city)...
As you might have guessed, this also works in a join.
SELECT olympics.olympiad ,olympics.host_city ,host_country.country_name FROM ( VALUES (2012, 'London') ,(2016, 'Rio de Janeiro') ,(2020, 'Tokyo') ,(2024, 'Paris') ,(2028, 'Los Angeles') ) as olympics(olympiad, host_city) INNER JOIN ( VALUES ('London', 'England') ,('Rio de Janeiro', 'Brazil') ,('Tokyo', 'Japan') ) as host_country(host_city, country_name) ON host_country.host_city = olympics.host_city
Because the TVC acts as a derived table, we can do some novel things we'll explore below.
A nice use of the Table Value Constructor is as an alternative to code like this.
SELECT 'USA' as country ,59939 as gdp_per_capita ,24.08 as percent_world_gdp UNION ALL SELECT 'China' ,8612 ,15.12 UNION ALL SELECT 'Japan' ,38214 ,6.02
The goal of this code is to generate a table or result set. We can refactor this code with the Table Value Constructor and get the same result.
SELECT * FROM ( VALUES ('USA',59939,24.08) ,('China' ,8612 ,15.12) ,('Japan' ,38214 ,6.02) ) as gdp(country,gdp_per_capita,percent_world_gdp)
Notice how it becomes more precise and less repetitive.
Interestingly, it's also possible to use the TVC as an alternative to the
LIKE operators because of this derived table functionality.
Here's an example using the regular
-- Setup data DROP TABLE IF EXISTS #high_cities CREATE TABLE #high_cities ( city VARCHAR(50) ,country VARCHAR(20) ,meters_above_sea_level INT ) INSERT INTO #high_cities(city,country,meters_above_sea_level) VALUES ('La Paz', 'Bolivia', 3869) ,('Cochabama', 'Bolivia', 2621) ,('Bogata', 'Colombia', 2601) ,('Addis Ababa', 'Ethiopia', 2361) ,('Mexico City', 'Mexico', 2316) ,('Xining', 'China', 2299) -- Regular IN operator SELECT * FROM #high_cities WHERE country IN ('Bolivia', 'Colombia')
And here's the same with the TVC. Each item of the
IN becomes a row inside a derived table created by the TVC. Then, filtering occurs by joining the derived table onto the target table.
SELECT #high_cities.* FROM #high_cities INNER JOIN ( VALUES ('Bolivia') ,('Colombia') ) as country_filter(country) ON country_filter.country = #high_cities.country
Keep in mind that the
IN operator is a syntax shortcut for multiple
OR conditions in the
WHERE clause. Depending on your data, this approach may turn out to be faster than using the
Likewise, the TVC can be used to replace multiple
SELECT * FROM #high_cities WHERE city LIKE '%a_a%' OR city LIKE 'M%'
As we saw before, we've turned each
LIKE condition into a row in a table and let the join do the filtering.
SELECT * FROM #high_cities INNER JOIN ( VALUES ('%a_a%') ,('M%') ) as city_search(search_string) ON #high_cities.city LIKE city_search.search_string
Lastly, there's a cool feature of the TVC that lets us reference an external column inside the TVC itself. This feature lends itself to succinctly turning columns into rows.
Hat tip to Dwain Camps on sqlservercentral.com for the great explainer on this technique.
In short, we
CROSS APPLY a Table Value Constructor where each column is a row in the TVC. The effect of this is we end up with a derived table of column values applied to each associated row.
-- Setup some data DROP TABLE IF EXISTS #avengers CREATE TABLE #avengers ( hero_name VARCHAR(20) ,intelligence INT ,strength INT ,speed INT ) INSERT INTO #avengers(hero_name,intelligence,strength,speed) VALUES ('Captain America', 85, 25, 30) ,('Iron Man', 90, 75, 50) ,('Black Panther', 85, 25, 25) -- Transpose columns into rows SELECT hero_name ,cols_to_rows.measure ,cols_to_rows.measure_value FROM #avengers CROSS APPLY ( VALUES ('intelligence', intelligence) ,('strength', strength) ,('speed', speed) ) as cols_to_rows(measure, measure_value)
Notice that the columns you're transposing will need to be the same or compatible data types for this to work.
As we've seen, the Table Value Constructor has some handy uses outside of the Multi-row Insert. Some of these can make your queries cleaner and faster. This expression is one of my favorite hidden gems in T-SQL.