Use Caution with SQL Servers MERGE Statement. Problem. SQL Server 2. SQL WHERE clauses Avoid CASE, use Boolean logic. As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express. General 1 Download OpenLDAP. If you want backsql support you will need to compile the program yourself. If you dont want backsql support, then the RPM should do fine. The OR in the WHEN clause of a CASE statement is not supported. How can I do this CASE ebv. WHEN 22978 OR 23218 OR 23219 THEN WECS 9500 ELSE WECS 9520. President Donald Trump remains at least relatively sure it is not him but everyone else who is the titanic idiot, per freshly hired goon and White House. Simple_Case_Statement_Results.jpg&container=focus' alt='Sql Update Case Else Do Nothing' title='Sql Update Case Else Do Nothing' />MERGE statement, which promised to be a simpler way to combine insertupdatedelete statements, such as those used during ETL extract, transform and load operations. However, MERGE originally shipped with several wrong results and other bugs some of which have been addressed, and some of which continue to exist even in the early preview releases of SQL Server 2. People also tend to make some leaps of faith regarding atomicity they dont realize that the single statement actually performs different operations separately, and thus can suffer from issues due to concurrency and race conditions just like separate statements can. Solution. I have been recommending that for now people stick to their tried and true methods of separate statements. The foreign key relation can be created either through SSMS GUI or TSQL. Rules for updatedelete operations may be specified explicitly. However if nothing is. Comparison of different SQL implementations. The goal of this page which is a work in progress is to gather information relevant for people who are porting. Adobe Creative Suite 4 Design Premium Serial Number. I wrote a TSQL Statement similar like this the original one looks different but I want to give an easy example here SELECT firstname CASE lastname WHEN null. SQL Server 2008 introduced the MERGE statement, which promised to be a simpler way to combine insertupdatedelete statements, such as those used during ETL extract. Sql Update Case Else Do Nothing' title='Sql Update Case Else Do Nothing' />Here are the major reasons. Bugs with the SQL Server Merge Statement. Update_2.png' alt='Sql Update Case Else Do Nothing' title='Sql Update Case Else Do Nothing' />It can be quite difficult to validate and guarantee that you are immune from any of the bugs that still exist. A few Connect items that you should be aware of, that are either still active, closed as Wont FixBy Design, or have only been fixed in specific versions often requiring a cumulative update or on demand hotfix. Some of these bugs can be worked around in the meantime using trace flags, but is the condensed format of MERGE really worth all of the extra testing that will require Also, keep in mind that this list is not necessarily exhaustive just look at how many bugs have actually been reported on Connect that I havent investigated and highlighted here. Also, to give an idea of how many bugs might still have gone undetected, check out these informative blog posts by Paul White to understand how hard these bugs can be to even notice, never mind track down and fix. SQL Server Merge Concurrency Issues. MERGE lookslike it will take care of concurrency for you, because implicitly it seems to a single, atomic statement. However, under the covers, SQL Server really does perform the different operations independently. This means that you could end up with race conditions or primary key conflicts when multiple users are attempting to fire the same MERGE statement concurrently. Dan Guzman went into a lot of detail in his blog post a few years ago, but basically this means that unless you use a HOLDLOCK hint on your MERGE target, your statement is vulnerable to race conditions. In reviewing customer code and questions out in the community, I dont recall ever coming across a HOLDLOCK hint naturally, except in cases where someone was demonstrating the very race condition Im talking about. The pattern should be MERGE dbo. Table. Name WITH HOLDLOCK AS target. USING. AS source. And not what I typically see MERGE dbo. Table. Name AS target. USING. AS source. SQL Server Merge Effect on Triggers. Due to those same mechanics, converting your insertupdatedelete code should be thoroughly tested when triggers are involved. Prevailing wisdom has instilled in most of us the mindset that, in SQL Server, triggers fire once per statement. However, with MERGE, this wisdom must be revisited, because its not quite true any longer. Lets take this simple example. SET NOCOUNT ON. CREATE TABLE dbo. My. Tableid INT. INSERT dbo. My. Table VALUES1,4. CREATE TRIGGER dbo. My. TableAll. ON dbo. My. Table. FOR INSERT, UPDATE, DELETE. PRINT Executing trigger. Rows affected RTRIMROWCOUNT. IF EXISTS SELECT 1 FROM inserted AND NOT EXISTS SELECT 1 FROM deleted. JPG' alt='Sql Update Case Else Do Nothing' title='Sql Update Case Else Do Nothing' />PRINT I am an insert. IF EXISTS SELECT 1 FROM inserted AND EXISTS SELECT 1 FROM deleted. PRINT I am an update. IF NOT EXISTS SELECT 1 FROM inserted AND EXISTS SELECT 1 FROM deleted. PRINT I am a delete. GONow, when I first started playing with MERGE, I expected the trigger to fire once, just like it does for any multi row operation. But this is not the case it actually fires the trigger for each operation that ends up happening as a result of the MERGE command. For example, with this MERGE MERGE dbo. My. Table WITH HOLDLOCK AS Target. USING VALUES1,2,3 AS Sourceid. ON Target. id Source. WHEN MATCHED THEN UPDATE SET Target. Source. id. WHEN NOT MATCHED THEN INSERTid VALUESSource. WHEN NOT MATCHED BY SOURCE THEN DELETE I expected this output, since my MERGE operation affected a total of 4 rows Executing trigger. Rows affected 4. I am an insert. I am an update. I am a delete. And then when I realized that the trigger gets fired multiple times, I expected this output, since I updated one row, inserted two new ones, and deleted one row Executing trigger. Rows affected 2. I am an insert. Executing trigger. Rows affected 1. I am an update. Executing trigger. Rows affected 1. I am a delete. What I got was surprising to me it made it seem like a hybrid between the trigger firing once and the trigger firing multiple times Executing trigger. Rows affected 4. I am an insert. Executing trigger. Rows affected 4. I am an update. Executing trigger. Rows affected 4. I am a delete. So, the lesson here is, if you already have triggers and youre switching to MERGE, dont rely on ROWCOUNT for anything. This is true also if you have your INSERT UPDATE DELETE triggers in different modules theyll still all report the total ROWCOUNT for the operation even when no rows are affected by that branch. So when you do make this switch youll want to invest in refactoring or at least heavily testing your triggers in all unit test outcomes. Improve Your Productivity or not with the SQL Server Merge StatementThis is a minor and subjective gripe, but Ive always found the Merge syntax quite daunting and difficult to memorize. I cant imagine there are many people out there who can write a fully featured MERGE statement without consulting Books Online, and without going through many more cycles of testing to verify that all branches function as expected. Conclusion. I am not trying to spread fear, sound like Chicken Little, or make you wary about any new feature that is introduced in SQL Server, however the increasing number of issues I see with MERGE make me feel obligated to document the potential problems, make sure that you are very well aware of them before deciding to use the syntax. Personally, I have converted a few implementations back to separate statements, because there came a point where I no longer felt I could trust the behavior of MERGE. If youre going to use it, please be careful Next Steps. Last Update 2. About the author. Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1. View all my tips.