MERGE ahead... Comparing MERGE performance to standard Inserts/Updates/etc

MS SQL Tips - Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE

Problem

Now that SQL Server 2008 has been out for quite awhile I've started to see a lot more use of the MERGE statement that became available in this version. While I do find it makes code more readable, I wondered whether or not there were any performance benefits or drawbacks to using this new statement as compared with just using the classic SELECT/INSERT/UPDATE/DELETE statements to complete the same task.

Solution

Test Setup

For this test we are going to compare the following four scenarios which at a high level insert a new record into a table or update it if it already exists (based on the primary key of the table). We will run each scenario twice, once against a table where the record being inserted/updated already exists and once when it doesn't.

Here are the scenarios:

1. SELECT record then INSERT or UPDATE based on result of SELECT statement
2. UPDATE record then SELECT update rowcount and INSERT based on result of SELECT statement
3. DELETE record then INSERT
4. MERGE record

..."

This reminded me of how much of a SQL Server 2008 lamer (I mean... um... noob... err... um... whatever) I am, that I've yet to use the new MERGE statement. Part of it is that my LOB system is still on SQL Server 2005 so I can't use it for in my day job, but that's still somewhat of a lame excuse. I need to get right with MERGE as I hate writing the "IF EXISTS Update ELSE Insert" statements. And 2008 has only been out for how long? sigh...