To serve the purpose of data synchronisation between two data table; as discussed in Part1, SQL Server 2008 was provided with a new feature of MERGE statement.
In this article we gonna go through the usage of MERGE statement/functionality and then briefly talk about the limitation/issues with that.
I think it’s better to take reference from the MSDN rather than replicating the same thing here again.
So here we go: http://msdn.microsoft.com/en-us/library/bb510625.aspx
A few main excerpts:
- Simple in use as UPDATE, INSERT & DELETE can be performed in one pass.
- At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
- The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.
- When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.
- For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. This may result into some errors or data inconsistency if not handled properly.
- Although, source table can be sourced from a query/temp table etc. which can be manipulated as per whether the whole table or a part of the table to be MERGE operation to be performed upon, but the target table is to be taken as the complete table – and therefore it’s almost impossible to identify the actual INSERTES/DELETES; only update can be performed properly in this case.
- Not so performance at times and very less scope to tweak the statement to make it perform better.
- Merge Conflicts / INSERT/UPDATE Race Condition: Let’s take an example where one record is intended to be inserted from source to target table since this particular records based upon the Primary Key/ Joining Key doesn’t exist at the target. The Merge statement under the default isolation level (Read Committed) will find it a potential Insert and will try to insert the same into the target table.
Now consider the scenario where another process tries to run the same merge procedure at the same time. This will also find the same record to be inserted.
But then when both of these processes (running in parallel) would try to insert the same record into the target table – Primary Key Violation error would be reported.
Actually, this shouldn’t be considered a bug. The atomicity of the MERGE transaction isn’t violated — it’s still an all-or-nothing behavior. So to prevent this conflict, a higher isolation level to be considered or table hint like WITH(HOLDLOCK) or WITH(SERIALIZABLE) to be used on the target table.
- Attempting to set a non-NULL-able column’s value to NULL: Sometimes while using the functions like ISNULL() or any window function into the ON clause of the MERGE statement, this error is seen. Even though we know that source doesn’t contain any NULL value for the non-nullable column it’s updating or inserting to, but still this error is reported at times.
This is a SQL Server bug in the MERGE statement. And there are many theories for the reason of this issue. One of them is that if query optimizer is having table spool in there, chances are high that it would result this error.