SQL Server 2008 T-SQL Enhancements

SQL Server 2008 included some great new T-SQL enhancements that I’ve been looking into and thought I would share.  The first is the combined variable declaration and initialization syntax: In previous versions of SQL Server, one would have to write the following in order to declare and initialize a variable:

declare @v_2005id int
set @v_2005id = 1
set @v_2005id = @v_2005id + 1

select @v_2005id as '@v_2005id'

Now in SQL 2008, this is streamlined:

declare @v_2008Id int = 1
set @v_2008Id +=1

select @v_2008Id as '@v_2008Id'

This is more abbreviated – always a good thing to save a few keystrokes.


Another enhancement is table constructors, which allows you to insert multiple rows within one SQL statement.  As an example:

declare @v_temp table (ItemName varchar(25) not null, ItemPrice numeric(5,2) not null)
insert into @v_temp (ItemName, ItemPrice) values ('A new product', 38.27), ('Another Product', 29.95), ('Product 1', 57.28)

select * from @v_temp

Notice that I inserted 3 rows into the temp table by simply surrounding each row “group” with () instead of writing 3 distinct INSERT statements.  The parenthesis are required between each insert “set” so that SQL Server can separate the individual insert lines. Once again, saving a few more keystrokes.


The next one is a bit more complicated – the combined Insert/Update/Delete statement, which is called MERGE in SQL Server.  This one allows you do all three operations within one statement, instead of three.  Let’s start by creating two tables and populating these two with data:

-- drop and (re)create the testing table
if exists (select 1 from sys.tables where [name] = 'tblPlayground')
begin
    drop table tblPlayground
end
go

create table tblPlayground
(
    Id                int            not null identity(1,1) primary key clustered
    ,ProductName    varchar(25)        not null
    ,ProductPrice    numeric(5,2)    not null    default 0
    ,CreateDate        date            not null    default SYSUTCDATETIME()
)
GO

insert into tblPlayground (ProductName, ProductPrice, CreateDate) values ('Product 1',27.95, '5/3/2009'), ('Product 2',33.47,'5/3/2009')

select * from tblPlayground

declare @v_temp table (ItemName varchar(25) not null, ItemPrice numeric(5,2) not null)
insert into @v_temp (ItemName, ItemPrice) values ('A new product', 38.27), ('Another Product', 29.95), ('Product 1', 57.28)

select * from @v_temp

In the above script, I created two tables and populated both of these with three rows each. There is one overlapping row between the two, two new rows in the second table, and one row in the first table that is not in the second table. What I want to do is merge the data in these two tables into the first table, with any missing rows in the second table being deleted from the first. With the new SQL Server MERGE command, I execute the following script:

Merge into tblPlayground as destination
using (select * from @v_temp) as secondary on (destination.ProductName = secondary.ItemName)
when matched then
    update set destination.ProductPrice = secondary.ItemPrice
when not matched by target then
    insert  values (secondary.ItemName, secondary.ItemPrice, GETDATE())
when not matched by source then
    delete; --<<-- ';' important!
    
-- after
select * from tblPlayground
go