What are the benefits of using the Row Constructor

问题:

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?

Thanks.

回答1:

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

回答2:

The answers to this question so far have been extremely misleading (and demonstrate a complete lack of effort/understanding) as 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.

回答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

回答4:

There is no performance benefit as Abe mentioned.

The order of the columns constructor is the required order for the values (or select statement). You can list the columns in any order - the values will have to follow that order.

If you accidently switch columns in the select statement (or values clause) and the data types are compatible, using the columns construct will help you find the problem.