Building and Maintaining a High Availability Infrastructure.

I am giving a hands on (code camp) presentation on setting up SQL 2008 Clustering.  If you are interested, click the link below and register. Will see you there.

http://codecamp.phillydotnet.org/2008-3/Lists/Sessions/DispForm.aspx?ID=17

Let us take a performance snapshot on using Row Constructors in SQL 2008

Example table:

create table Customer
(customerID int primary key clustered identity(1,1)
,firstName varchar(30)
,lastname varchar(30)
, age int
)

-- SQL 2005 method

begin transaction
insert into customer values ('Joe', 'Smith', 35)
insert into customer values ('Jane', 'Smith', 36)
insert into customer values ('Daniel', 'Smith', 37)
insert into customer values ('Daniel', 'Smith', 37)
commit transaction;

-- SQL 2008 method (row constructors)

begin transaction
insert into customer values
('Joe', 'Smith', 35),
('Jane', 'Smith', 36),
('Daniel', 'Smith', 37),
('John', 'Smith', 38)
commit transaction;

As you examine the different variables, you will notice that SQL 2008 Row constructor method fares much better for the scenario described.

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. 

Developer centric SQL 2008 Features:

I am giving a walk through on developer centric features in SQL 2008. If you are interested to learn more about the new SQL 2008 features available for developers like Row constructors, HierarchyID and more, you can register on the link shown below: 

http://www.phillydotnet.org/Default.aspx?tabid=679