Table Valued Constructors (TVC)

The other day while studying I had the need to create a table and populate it with a small set of random data. I just so happened to have "Show Actual Execution Plan" enabled and when I executed the insert statement to populate the table (using a UNION SELECT) I was quite shocked at the plan generated.

This got me thinking that there must be a better, more efficient way to populate a table with data.

The set-up:

The set-up here is very basic. I wanted to test an insert into a table that had a clustered index (so we can see the effect of the insert which needed to update the index). I didn't care what the values were or what the index was for the purpose of this test.

Create a test table:

USE tempdb GO CREATE TABLE tempvalues (

Value1 NUMERIC(5,5) ,Value2 NUMERIC(5,5) ,Value3 NUMERIC(5,5) ,CONSTRAINT [PK_tempvalues] PRIMARY KEY CLUSTERED (Value1, Value2, Value3)); GO

Insert some data using UNION SELECT

INSERT INTO tempdb..tempvalues (Value1, Value2, Value3) SELECT 0.13601, 0.11622, 0.40041 UNION SELECT 0.28688, 0.33935, 0.58548 UNION SELECT 0.66694, 0.78595, 0.458 UNION SELECT 0.18889, 0.34067, 0.75255;

Here is the execution plan using a UNION SELECT:

As you can see there is a "Constant scan" operator for each row you are inserting. Now suppose you are doing this for 500 rows. Here is a partial extract of what the execution plan would look like:

As you can see there are now 500 "Constant Scan" operators which has flooded the graphical execution plan!

As we are working with such a small dataset, a more preferable way to do this kind of insert would be to use a Table Value Constructor (TVC).

What is a TVC? Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING <source table> clause of the MERGE statement, and in the definition of a derived table in the FROM clause.

Source: http://msdn.microsoft.com/en-us/library/dd776382(v=sql.105).aspx

Where would you use a TVC? When constructing a static data set for insertion or selection. E.G. Reference data scripts.

Notable Limitations? You can only specify a maximum of 1000 rows in a TVC.

TVC Syntax:

INSERT INTO tempdb..tempvalues (Value1, Value2, Value3) SELECT Value1 ,Value2 ,Value3 FROM ( VALUES (0.13601,0.11622,0.40041) ,(0.28688,0.33935,0.58548) ,(0.66694,0.78595,0.458) ,(0.18889,0.34067,0.75255) ) AS x (Value1, Value2, Value3)

Here is what the execution plan looks like:

And even with 500 rows, the execution plan is the same:

Here are some stats to show the efficiency different between the 2 methods:

UNION SELECT (Inserting 500 rows)

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.