Have you ever needed to merge data from two tables? You probably first inserted the rows that don't exist in target table then tried to match existing rows from target table with rows from source table and then update target with values from source and in the end you did something with untouched target rows.

Let's create two tables and fill them in with some values.

create table a (

    id int primary key clustered

    ,sometext varchar(100) not null

)

create table b (

    id int primary key clustered

    ,sometext varchar(100) not null

)

insert into a (id, sometext) values (1, 'first'),(2, 'second'),(4, 'fourth')

insert into b (id, sometext) values (1, 'first'),(3, 'third'),(4, 'fourth-but-changed')

 Before SQL Server 2008 you needed to write a couple of statements to achive the goal (let's do all manipulations in transaction in order to not fill in test tables again). So the next query performs merge of two tables in SQL Server 2000 or SQL Server 2005

begin tran

insert into a

select b.id, b.sometext

from b

where not exists ( select 1

                   from a as a2

                   where a2.id=b.id)

update a set

    a.sometext = b.sometext

from a

    inner join b on a.id = b.id

   

update a set

    a.sometext = a.sometext + ' not present in b'

from a

    left outer join b on a.id = b.id

where b.id is null

 

select * from a

rollback

 In new version of SQL Server you can do the same in much more compact, crleare and easy way using merge operator

begin tran

merge a as a

using b

on a.id = b.id

when matched then update set sometext=b.sometext

when not matched by target then insert (id, sometext) values (b.id, b.sometext)

when not matched by source then update set sometext += ' not present in b'

;

select * from a

rollback 

 Have you noticed semicolon after merge statement? T-SQL operator MERGE reguires semicolon in the end of statement otherwise you will be getting the error

Msg 10713, Level 15, State 1, Line 18

A MERGE statement must be terminated by a semi-colon (;).

 The full syntax of MERGE is 

[ WITH <common_table_expression> [,...n] ]

MERGE

        [ TOP ( expression ) [ PERCENT ] ]

        [ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]

        USING <table_source>

        ON <merge_search_condition>

        [ WHEN MATCHED [ AND <clause_search_condition> ]

            THEN <merge_matched> ]

        [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

            THEN <merge_not_matched> ]

        [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

            THEN <merge_matched> ]

        [ <output_clause> ]

        [ OPTION ( <query_hint> [ ,...n ] ) ]   

;

Read BOL for the details.

 Variable declaration and synchronous assignment

 Before sql server 2008 all stored procedures had beginning something like 

declare

    @a int

    ,@b int

    ,@c varchar(100)

 

select

    @a = 0

    ,@b = 0

    ,@c = ''

 With sql server 2008 you can declare and assign initial values to your variables at the same time within one statement.

declare

     @a int = 0

    ,@b int = 0

    ,@c varchar(100) = ''

  And what is faster concerning sql server engine? You can check it using 2 queries generated by scripts

declare

     @i int = 0

    ,@c varchar(max) = ''

    ,@d varchar(max) = ''

while @i < 100

begin

set @c += 'declare @a' + CONVERT(varchar(10), @i) + ' int' + CHAR(13) + CHAR(10)

set @d += 'set @a' + CONVERT(varchar(10), @i) + ' = 0' + CHAR(13) + CHAR(10)

set @i+=1

end

print @c

print @d

 

and 

declare

     @i int = 0

    ,@c varchar(max) = ''

while @i < 100

begin

set @c += 'declare @a' + CONVERT(varchar(10), @i) + ' int = 0' + CHAR(13) + CHAR(10)

set @i+=1

end

print @c

 You can see then in profiler that first script takes a bit more time than second one, that uses new declare and assign feature.

The next step for t-sql improvement is constant values declaration.

New compound  operators +=, -=, *= /= etc.

For those who are used to this kind of operators from common programming languages (like C++, Java etc) Microsoft provides ability to use them inside transact-sql scripts. This works the same here and does not require too much description, see script above for example.

Constructor of table values though VALUES clause

This is easier to show than explain. Have you needed to insert into table more than one row? It used to be eirther multiple insert statements or single insert / select statement. Now it became much easier

declare @t table (i int, v varchar (10))

insert @t (i, v) values (1,'aaa'), (2,'bbbb'), (3,'ccccc')

select * from @t

Furthermore, you can select from this values construction and join it with other tables.

select *

from (

 values

 (1,'aaa')

 ,(2,'bbbb')

 ,(3,'ccccc')

 )a (i, v)

  GROUP BY GROUPING SETS clause

to be continued