SQL 2008 Language Features (1)

1. Single-statement var declaration and initialization
DECLARE @MyVar INT = 10, @YourVar VARCHAR(10) = 'blabla';
SELECT @MyVar, @YourVar

2. C-type assignment ops
DECLARE @X INT = 10, @Y INT = 11;
SELECT @X += 5;
SELECT @X;
SELECT @X &= @Y
SELECT @X;
SELECT @X ^= @Y
SELECT @X;

These operators inlcude:
mathematical operation
+=, -=, *=, /=, %=

Bitwise operation
^=, |=, &=

3. INSERT multiple records
CREATE TABLE #T (Name VARCHAR(10));
INSERT INTO #T
VALUES ('Kevin'),('Jack'),('George')
SELECT * FROM #T
DROP TABLE #T

4. Merge statement - to implment "upsert" logic
CREATE TABLE #T1 (ID INT, Name VARCHAR(10));
CREATE TABLE #T2 (ID INT, Name VARCHAR(10));

INSERT INTO #T1 VALUES (1, 'Kevin'), (2, 'Jack') , (3, 'George');
INSERT INTO #T2 VALUES (1, 'John'), (3, 'Jack') , (4, 'Steven');

MERGE #T1 AS t USING #T2 AS s
ON (t.ID = s.ID)
WHEN MATCHED THEN
UPDATE SET NAME = s.NAME
WHEN NOT MATCHED THEN
INSERT (ID, Name) VALUES (s.ID, s.Name);

SELECT * FROM #T1;
DROP TABLE #T1;
DROP TABLE #T2;

In MERGE statement, there are three types of matches:
WHEN MATCHED [ AND other_conditions]
WHEN NOT MATCHED [ BY TARGET ] [ AND other_conditions]
WHEN NOT MATCHED BY SOURCE [ AND other_conditions]

For the unmatched, the default case is "BY TARGET", otherwise we have to explicitly specify BY SOURCE.  We know LEFT OUTER JOIN and RIGHT OUTER JOIN, so we understand what that is talking about.
However with optional extra conditions affixed to them, we are free to define as many cases as we need. In case there are inclusive cases, we need take attention to handled them in order. e.g.,

WHEN MATCHED AND s.Name='George' THEN ...
WHEN MATCHED THEN ...

The cases are processed in a sequential order - the result of prior case is the context of the latter case, and so forth.

When a condition falls in any matching case, there are three type of special operations, INSERT, UPDATE or DELETE, - by special, I mean these statments inherit MERGE statement context that it won't have when not within a MERGE statement. E.g., just "DELETE" is enough to mean delete from the target table the current record, we don't need to specify the table and the condition criteria explicitely.

Of course we can add any general statements in any of the matching blocks.