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.
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:
- SELECT record then INSERT or UPDATE based on result of SELECT statement
- UPDATE record then SELECT update rowcount and INSERT based on result of SELECT statement
- DELETE record then INSERT
- 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...