A new member of the T-SQL Family - the MERGE statement

One revolutionary addition is the new MERGE statement. On par with other core T-SQL CRUD features such as INSERT, SELECT, UPDATE, and DELETE, the MERGE statement is an ISO-2003 compliant command that is primarily intended to handle what many database users refer to as "UPSERT" functionality. For example, say you have an application where you either need to log a new entry for something that hasn't been added to your system, or update it if it's already been added previously. Without the MERGE statement you must either run a SELECT statement to see if a row has already been logged and then UPDATE or INSERT if it's not there. Or you can try to UPDATE first, and then INSERT if the UPDATE doesn't affect any rows. With the MERGE statement you can do this all in one fell swoop. Here's a rather extensive example of the syntax, pulled from the SQL Server November CTP:

MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = GETDATE()
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty <> 0
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty = 0
THEN DELETE;

Documentation from a previous CTP's Books Online provides an overview, but the syntax has changed a bit between CTP releases. However, if you study that statement listed above, you can see that it's just a built-in switch (or case) that specifies what to check for, and then what to do if the data is found or not. What's cool about this statement, though, is that you can perform the entire check-in a single operation - which drastically increases performance during large-scale UPSERT operations or when you're trying to merge two large tables.

Table-Valued Parameters
Ever wanted - or more importantly - needed to pass in a table or array of values to your stored procedures? Well, now you can with Table-Valued Parameters. Imagine the following:
CREATE TYPE myTable AS TABLE (
key varchar(6),
value varchar(20)
)
GO

CREATE PROC dbo.InsertKeyValuesFromWebApp
@tvp myTable READONLY,
@owner int
AS
SET NOCOUNT ON

INSERT INTO [dbo].[SomeTable] (key, value, owner)
SELECT key, value, @owner FROM @tvp

RETURN 0
GO

For simpler applications, this functionality might seem like overkill, but for many complex applications, having this code on hand will be a lifesaver. This is something I’ve wanted for years (and which I’ve had to get around by sending in XML fragments as varchar parameters and then re-hydrating on the server) , and now we’ll have native support for it – I can’t wait. I can also see this becoming a wicked bit of functionality when combined with LINQ down the road.