Row Constructors in SQL 2008

Let us delve into using Row Constructors

Two basic tables for test purposes

— Customer

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
DROP TABLE [dbo].[Customer]
GO
create table Customer
(firstname varchar(30)
,lastname varchar(30)
)

— Customer1

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer1]') AND type in (N'U'))
DROP TABLE [dbo].[Customer1]
GO
create table Customer1
(firstname varchar(30)
,lastname varchar(30)
, age int
)

Use with INSERT statement to insert multiple rows as an atomic operation:

— SQL 2005 method
insert into Customer (FirstName, LastName) values (Alex, 'Smith')
go

insert into Customer (FirstName, LastName) VALUES ('John', 'Smith')
go

— another SQL 2005 method

insert into Customer (FirstName, LastName)
select 'Alex', 'Smith'
union all
select 'John', 'Smith'

— Rewriting with SQL 2008

INSERT INTO Customer (FirstName, LastName)
VALUES
 ('Alex', 'Smith'),
 ('John', 'Brown'),
 ('Daniel', 'Williams');

Using Row Constructors as a DataSource – On the Fly!

SELECT *
  FROM
     (
       VALUES

       ('John','Smith')

      ,('Alex','Smith')

     ) Customer(FirstName,LastName)

As  you see above, I just created a datasource named Customer on the fly. Try running this and you will see that the datasource is treated as a table here (not created on SQL Server though).

 

Another example of Row constructors as data source

select c.firstname
, c.lastname
from Customer c
join
(
 values('John','Smith'),('Mary','Smith'),('John','Williams')
) customerTemp(FirstName,LastName)
on c.firstname = customerTemp.firstname 

Ability to include scalar subqueries as elements in row constructors

insert into Customer1
values
('Alex','Smith',30),
('Robert','Smith',35)

insert into Customer(firstname, lastname)
values(
(select top 1 firstname from Customer1),
(select top 1 lastname from Customer1)
)

I will try to do some benchmarks on using Row Constructors on my next post.