Table Value Constructors in SQL Server 2008

Table Value Constructors (TVCs) are a useful feature of 2008, allowing you to specify tables of values and expressions. This has all sorts of uses. Users who are stuck with previous versions of SQL Server can play along, since Rob demonstrates that there have, for a long time, been ways of doing this in SQL Server, though less elegantly.

Beginning with SQL Server 2008, you can define table value constructors (TVCs) within your data manipulation language (DML) statements so you can work with subsets of data in a tabular format. A TVC is an expression made up of a VALUES clause and one or more row value expressions, each of which returns a scalar value. Taken together, these values provide data to the DML statement in much the same way a subquery might return a table.

A TVC is made up of one or more columns and one or more rows of data. You can use a TVC as part of the VALUES clause in an INSERT statement, the USING clause in a MERGE statement, or the FROM clause in any DML statement. The following syntax shows the elements that make up a TVC:

Each TVC begins with the VALUES keyword, followed by one or more row value expressions (the part of the syntax enclosed in curly brackets). A row value expression can return only one value. You arrive at that value by defining an expression, specifying the NULL keyword (to return a null value), or specifying the DEFAULT keyword (to insert the default value in the column being targeted).

For each value you want to return in a TVC row, you must include a row value expression. If there are more than one row value expressions in a row, you separate them with commas. For each row you want to return, you must enclose the row value expressions in parentheses and separate the rows with commas, if there is more than one row. Essentially, you are using a TVC to construct a table made up of values that are organized into columns and rows. That means the number of values must be the same for each row you define.

The best way to understand how to create TVCs is to see them in action, so let’s look at a few examples. Note that all the examples target the Product table, which I created by using the following Transact-SQL code:

I created the Product table in the AdventureWorks2008R2 database on a local instance of SQL Server 2008 R2. The examples either insert data into the table or return data from that table.

NOTE: The examples are presented in the order I ran them. If you run them in a different order, you’ll get different results. For instance, the first few examples insert data into the Product table, followed by examples that retrieve the data. If you try to retrieve the data before you insert it, your SELECT statements will return no rows.

Using a Table Value Constructor in an INSERT Statement

The first example we’ll look at uses a TVC in an INSERT statement to add several rows of data to the Product table:

The first thing you’ll notice is that this looks a lot like your traditional INSERT statement, except that there are multiple sets of parentheses in the VALUES clause. The VALUES clause, not surprisingly, begins with the VALUES keyword and is followed by the data to be inserted into the Product table. Each set of parentheses represents a row of data, and each row (enclosed in the parentheses) includes the individual row value expressions.

For example, the first row includes three row value expressions: Mountain-300, 101, and 12. In this case, the expressions are all constants. Notice that the string value is enclosed in single quotes and the three values are separated by commas. In addition, a comma is used after the closing parenthesis to separate this row from the next.

You’ll wonder how you got along before having such a useful construct. Reasonably well, it turns out, since the code:

…is equivalent and runs on all versions of SQL Server.

When the TVC is taken as a whole, what you have is a table that includes three columns and seven rows of data. The INSERT statement will add these seven rows of data to the Product table. If you were then to retrieve the data from the table (via the SELECT statement I’ve included in the example), you’d get the following results:

ProductID

ProductName

CategoryID

ProductAmt

1

Mountain-300

101

12

2

Mountain-400

101

8

3

Road-250

101

8

4

Road-350

101

14

5

Helmet-sport

102

16

6

AWC Logo Cap

103

11

7

AWC Logo Jersey

103

18

As you can see, the data have been added to the ProductName, CategoryID, and ProductAmt columns. ProductID is an IDENTITY column, so the database engine assigned those values automatically.

That’s all there is to using a TVC in your INSERT statement. However, as you’ll recall from the TVC syntax above, you can also pass in the DEFAULT and NULL keywords for your row value expressions. For example, the following INSERT statement uses both of these keywords when adding three more rows to the Product table:

Notice that in the first row of the TVC, I’ve included the DEFAULT keywords as the second row value expression. In the third row, I’ve included the NULL keyword as the third row value expression. The SELECT statement now returns the following results:

ProductID

ProductName

CategoryID

ProductAmt

1

Mountain-300

101

12

2

Mountain-400

101

8

3

Road-250

101

8

4

Road-350

101

14

5

Helmet-sport

102

16

6

AWC Logo Cap

103

11

7

AWC Logo Jersey

103

18

8

Mountain-500

101

10

9

Road-450

101

6

10

Helmet-racing

102

NULL

As you would expect, the three rows have been added to the table. The row with the ProductID value of 8 has a CategoryID value of 101, which is the default value defined on that column. For the row with the ProductID value of 10, the ProductAmt value has been set to null. The use of the DEFAULT keyword is a useful feature that was not available to the old syntax that used UNION ALL.

So far, the row value expressions shown in the preceding two examples have been made up only of constants or the DEFAULT and NULL keywords. However, your row value expression can also be a variable or a more complex expression. For example, in the following INSERT statement, I include a subquery as one of the row value expressions:

As you can see, the second row value expression in the second row of the TVC is a subquery that returns a scalar value-the category ID of the product Helmet-racing.

NOTE: The subquery assumes that the product names are unique, in which case only one value is returned. I could have also specified the DISTINCT keyword, but that assumes that, if there are multiple products with the same name, they would always be associated with the same category ID. I would do better to use TOP 1 categoryID or MAX (CategoryID) to ensure a scalar subquery. For the purposes of this example, we’ll assume that the subquery will return only one value-or that we can rewrite it to ensure that it does.

The subquery’s value is then included in the TVC results just like the other row value expressions and inserted into the table. The SELECT statement I’ve tagged on to the example above returns the following results:

ProductID

ProductName

CategoryID

ProductAmt

1

Mountain-300

101

12

2

Mountain-400

101

8

3

Road-250

101

8

4

Road-350

101

14

5

Helmet-sport

102

16

6

AWC Logo Cap

103

11

7

AWC Logo Jersey

103

18

8

Mountain-500

101

10

9

Road-450

101

6

10

Helmet-racing

102

NULL

11

Mountain-600

101

10

12

Helmet-touring

102

14

As you can see, the Helmet-racing product has a CategoryID value of 102. That same value is used as the category ID for the Helmet-touring product because that’s the ID returned by the subquery.

Using a Table Value Constructor in a SELECT Statement

The examples above provide you with all the basics you need to build a TVC. However, you’re not limited only to the VALUES clause of an INSERT statement. In the following SELECT statement, I use a TVC as the table expression in the statement’s FROM clause:

As you can see, the TVC has the same format you saw in the preceding examples: each row is enclosed in a set of parentheses, and the two values in each row are separated with a comma. As a result, the TVC returns a table that includes two columns and three rows.

The SELECT statement can now use the TVC as it would any other table in the FROM clause. Because the SELECT clause specifies that all columns should be returned by the statement and there are no other qualifiers to limit the results (such as a WHERE clause), the statement returns all the data defined in the TVC, as shown in the following results:

CategoryID

CategoryName

101

Bikes

102

Accessories

103

Clothes

As you would expect, the SELECT statement returns a result set made up of two columns named CategoryID and CategoryName and three rows of data.

Using a TVC in a FROM gets much more interesting when you include other table expressions in that clause. For example, the following SELECT statement defines an inner join that includes the Product table and the TVC you saw in the preceding example:

Notice that I’ve defined the join based on the CategoryID column in the Product table and in the TVC. In addition, I’ve included only two columns in the SELECT list: the ProductName column from the Product table and the CategoryName column from the Category TVC. The SELECT statement returns the following results:

ProductName

CategoryName

AWC Logo Cap

Clothes

AWC Logo Jersey

Clothes

Helmet-racing

Accessories

Helmet-sport

Accessories

Helmet-touring

Accessories

Mountain-300

Bikes

Mountain-400

Bikes

Mountain-500

Bikes

Mountain-600

Bikes

Road-250

Bikes

Road-350

Bikes

Road-450

Bikes

As you would expect, the SELECT statement matched the product names to the category names. The TVC, in this case, acted just like any other table expression you could have defined in the FROM clause. Once again, this functionality has been in previous versions of SQL Server, but with the slightly more awkward syntax of UNION ALL.

Using a Table Value Constructor in a MERGE Statement

Another interesting way to use a TVC is within the USING clause of a MERGE statement. For example, in the following MERGE statement, I use a TVC to either update data in the Product table or insert data into that table:

The TVC defines fours rows of data with three columns each (ProductName, CategoryID, and ProductAmt). The TVC serves as the source table that contains the values necessary to modify the target table (Product). The MERGE statement compares the ProductName values in the source table with the ProductName values in the target table. (Again, this assumes that product names are unique.) If the product names match, the ProductAmt column is updated but the rest of the row is untouched. If the product names don’t match, the TVC row is inserted into the table.

After running the MERGE statement, the SELECT statement returns the following results:

ProductID

ProductName

CategoryID

ProductAmt

1

Mountain-300

101

17

2

Mountain-400

101

8

3

Road-250

101

13

4

Road-350

101

14

5

Helmet-sport

102

16

6

AWC Logo Cap

103

11

7

AWC Logo Jersey

103

18

8

Mountain-500

101

10

9

Road-450

101

6

10

Helmet-racing

102

15

11

Mountain-600

101

10

12

Helmet-touring

102

14

13

Mountain-700

101

7

14

Road-550

101

9

It turns out that the MERGE statement updated the ProductAmt values for the Mountain-300, Road-250, and Helmet-racing products. However, the statement added rows for the Mountain-700 and Road-550 products because those products did not match any of the product names that already existed within the table.

Conclusion

As you can see, TVCs can be used in a number of ways within your DML statements. Keep in mind, however, that a TVC is limited to 1000 rows, and, as stated earlier, each row value expression must return a scalar value, otherwise you’ll receive a syntax error when you try to run your statement. TVCs represent a simplification of syntax rather than new functionality. You can do almost everything you can with a TVC in previous versions of SQL Server, barring the use of the DEFAULT keyword.

You can find more information about TVCs by referring to the topic “Table Value Constructor (Transact-SQL)” in SQL Server Books Online. There you will also find additional examples of the various ways to use TVCs.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Tags: , , , , ,

  • 33352 views

  • Rate
    [Total: 75    Average: 4.1/5]
  • David McKinney.

    Nice one.
    Thanks Robert for the clear roundup.

    This is the first time I’ve seen UNION ALL written in this way.

    SELECT 101, ‘Bikes’
    UNION SELECT ALL 102, ‘Accessories’
    UNION SELECT ALL 103, ‘Clothes’

    I’ve always written it as

    SELECT 101, ‘Bikes’
    UNION ALL SELECT 102, ‘Accessories’
    UNION ALL SELECT 103, ‘Clothes’

    hmmm….not sure which one I prefer.

  • Anonymous

    aha…confused now.
    I see now that it’s not the same thing!

    select all 1
    UNION
    select all 1 — returns 1 row whereas

    select all 1
    UNION all
    select all 1 — returns 2 rows.

  • David McKinney.

    aha…confused now.
    If I’m not mistaken, you should have written

    SELECT 101, ‘Bikes’
    UNION ALL SELECT 102, ‘Accessories’
    UNION ALL SELECT 103, ‘Clothes’

    as the ALL of the type “SELECT ALL 102, ‘Accessories'” has no effect. It’s the default alternative to DISTINCT.

  • Andrew Clarke

    Re confused
    That was a wierd corruption in the setting phase of the article. Fixed now: Don’t even ask how that crept in but all code was checked and executed beforehand!

  • Jeff Moden

    … and they talk about “portability”. 😉
    Nice article, Robert. Well done.

    “…is equivalent and runs on all versions of SQL Server.”

    That’s exactly why I don’t use TVC’s in any of my posts on SQLServerCentral especially on really simple coded answers. Since 2k5 and 2k8 have a lot of the same functionality everywhere else, it just makes life easier on everyone.

  • Articles

    bujjinan@gmail.com
    using your article i definitely,knew a lot of new things about table constructions.. really very good information given to all of us….
    Thanking you…
    _____________________
    saritha.

  • Keith

    Awesome…
    I was using an IN statement with a list of values. I replaced the IN statement with a JOIN to a VALUES statement and a list of my values. Query returned in 30 seconds as opposed to before where it just spun for two minutes.

    Awesome.

  • Thiago Dantas

    cool
    never knew you could use VALUES inside the from clause, seems like a weird syntax but nice to know its there