SQL Server 2008: Row Value Constructor

Definitely, we were sometimes in a need of a new technique to insert data massively in SQL Server 2005 and previous versions. We used to do it by using traditional techniques like UNION ALL and individual INSERT statements. This story has ended up with the arrival of a new feature in SQL Server 2008 which allows to insert much data with only one INSERT statement, that is, Row Value Constructor.

To begin with, we are going to illustrate this new feature by creating a new table where four data rows will be inserted.

CREATE TABLE [Production].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileName] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileExtension] [nvarchar](8) COLLATE Latin1_General_CS_AS NOT NULL,
    [Revision] [nchar](5) COLLATE Latin1_General_CS_AS NOT NULL,
    [ChangeNumber] [int] NOT NULL CONSTRAINT [DF_Document_ChangeNumber]  DEFAULT ((0)),
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) COLLATE Latin1_General_CS_AS NULL,
    
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Document_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Document_DocumentID] PRIMARY KEY CLUSTERED 
(
    [DocumentID] ASC
)
) ON [PRIMARY]

At first, we will do it in SQL Server 2005. This is the traditional and old-fashioned way (one INSERT statement per row): 

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...')

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL)

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...')

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...')

This is the cumbersome way by using UNION ALL clause which has sometimes poor performance:

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
SELECT 'Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...'
UNION ALL
SELECT 'Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL
UNION ALL 
SELECT 'Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...'
UNION ALL
SELECT 'Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...'

Now in SQL Server 2008 we can use Row Value Constructor feature (only one INSERT statement to insert four data rows):

INSERT INTO [Production].[Document](  
      [Title]
      ,[FileName]
      ,[FileExtension]
      ,[Revision]
      ,[ChangeNumber]
      ,[Status]
      ,[DocumentSummary]
)
VALUES ('Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...'),
('Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL),
('Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...'),
('Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...')

As you have noticed, we do not need to use UNION ALL any longer, it allow to save a lot of time and work. It is truly useful when we need to insert much data and it also offers a remarkable performance. I hope you enjoy this new feature. Thanks for reading again!