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?

Kittoes0124 · Answer 1 · 2020-06-02T15:16:13.877

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.

There can also be a significant performance benefit if a large number of individual INSERT statements would be sent through a network connection. — Gord Thompson, Oct 19 '16 at 20:57
@GordThompson Most definitely. If you're inserting 10,000 rows over the network with individual statements then, at minimum, you are wasting the 240,000 bytes required to send just the string `insert into ` over the wire 10,000 times. Every character you add after that for the table/column names is two more bytes that have to be sent, received, and parsed before any of the stuff written in my post above starts to apply... — Kittoes0124, Oct 19 '16 at 21:14

Abe Miessler · Accepted Answer · 2018-07-27T15:02:16.593

2

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

edited Jul 27 '18 at 15:02

answered Mar 29 '12 at 18:45

Abe Miessler

82,532
99
305
486

2

Actually, there can be a significant performance benefit if a large number of individual INSERT statements would be sent through a network connection. See [my answer here](http://stackoverflow.com/a/25879264/2144390) for an example. – Gord Thompson Oct 19 '16 at 20:04
Edited a wrong answer to make it correct after many years ? :) It looks like it was a pity the only correct answer to this question on this thread was from Kittoes1024 and not marked as an answer (that makes me think OPs answer marks should be corrected by moderators). – Cetin Basoz Jul 27 '18 at 15:11

score 1 · Answer 3 · answered Mar 29 '12 at 18:53

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

score -2 · Answer 4 · answered Mar 29 '12 at 19:39

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.

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

4 Answers4

Linked