What are the benefits of using the Row Constructor syntax in a T-Sql insert statement?

Question

In SQL Server 2008, you can use the Row Constructor syntax to insert multiple rows with a single insert statement, e.g.:

insert into MyTable (Col1, Col2) values
  ('c1v', 0),
  ('c2v', 1),
  ('c3v', 2);

Are there benefits to doing this instead of having one insert statement for each record other than readability?

Answer 1

Aye, there is a rather large performance difference between:

declare @numbers table (n int not null primary key clustered);

insert into @numbers (n)
values (0)
     , (1)
     , (2)
     , (3)
     , (4);

and

declare @numbers table (n int not null primary key clustered);

insert into @numbers (n) values (0);
insert into @numbers (n) values (1);
insert into @numbers (n) values (2);
insert into @numbers (n) values (3);
insert into @numbers (n) values (4);

The fact that every single insert statement has its own implicit transaction guarantees this. You can prove it to yourself easily by viewing the execution plans for each statement or by timing the executions using set statistics time on;. There is a fixed cost associated with "setting up" and "tearing down" the context for each individual insert and the second query has to pay this penalty five times while the first only pays it once.

Not only is the list method more efficient but you can also use it to build a derived table:

select *
from (values
    (0)
  , (1)
  , (2)
  , (3)
  , (4)
) as Numbers (n);

This format gets around the 1,000 value limitation and allows you to join and filter your list before it is inserted. One might also notice that we're not bound to the insert statement at all! As a de facto table, this construct can be used anywhere a table reference would be valid.

Answer 2

Yes - you will see performance improvements. Especially with large numbers of records.

Answer 3

If you will be inserting more than one column of data with a SELECT in addition to your explicitly typed rows, the Table Value Constructor will require you to spell out each column individually as opposed to when you are using one INSERT statement, you can specify multiple columns in the SELECT.

For example:

USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.MyProducts (Name varchar(50), ListPrice money);
GO
-- This statement fails because the third values list contains multiple columns in the subquery.
INSERT INTO dbo.MyProducts (Name, ListPrice)
VALUES ('Helmet', 25.50),
       ('Wheel', 30.00),
       (SELECT Name, ListPrice FROM Production.Product WHERE ProductID = 720);
GO

Would fail; you would have to do it like this:

INSERT INTO dbo.MyProducts (Name, ListPrice)
VALUES ('Helmet', 25.50),
       ('Wheel', 30.00),
       ((SELECT Name FROM Production.Product WHERE ProductID = 720),
        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));
GO

see Table Value Constructor Limitations and Restrictions