I have been doing some testing with the February CTP of SQL Server 2008 (aka Katmai), comparing the performance of the new TSQL MERGE command to the classic "Upsert" pattern of trying an UPDATE, checking the @@ROWCOUNT and then doing an INSERT if no rows were updated.
The "Upsert" pattern is very efficient if the row you are trying to update already exists in the table, but not so good if it does not (since it tries to find the row, then does an INSERT). What I have found with my MERGE testing is that MERGE has about the same cost as "Upsert" if the row already exists, but is about half the cost of "Upsert" if the row does not exist (so you have to do the INSERT).
This makes MERGE a win/win situation compared to "Upsert".
-- This is an "Upsert" pattern ALTER PROCEDURE [dbo].[UpsertPostByPostID] ( @PostID bigint, @Description varbinary(MAX) ) AS SET NOCOUNT ON; -- Try Update first UPDATE dbo.PostEx SET [Description] = @Description WHERE PostID = @PostID --Nothing updated, insert instead IF @@ROWCOUNT = 0 BEGIN INSERT INTO dbo.PostEx (PostID, [Description]) VALUES (@PostID, @Description) END RETURN
-- This is a MERGE ALTER PROCEDURE [dbo].[MergePostByPostID] ( @PostID bigint, @Description varbinary(MAX) ) AS SET NOCOUNT ON; MERGE dbo.PostEx AS cp USING (SELECT @PostID AS pPostID, @Description AS pDescription)AS s ON cp.PostID = s.pPostID WHEN MATCHED AND (cp.[Description] <> s.pDescription) THEN UPDATE SET [Description] = s.pDescription WHEN NOT MATCHED THEN INSERT VALUES (pPostID, pDescription); RETURN