Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: food for thought regarding the MERGE statement

    I am more and more leaving the use of UPDATE statements (when joins with other tables are involved), in favour of MERGE statements.

    I recently found this article about bugs with the MERGE statement in SQL Server.

    I agree with his observation that the MERGE statement is a complex one. I often find myself first writing an UPDATE statement, then turn it into a MERGE one, checking and rechecking BOL for the correct syntax.

    A MERGE statement is not atomic, triggers behave differently, ...
    I wonder more and more: is it worth using?

    AFAIK there is no alternative for the MERGE statement to get the id's of set based INSERTS, when the id is an IDENTITY column. I'll keep using it in those cases.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There definitely are "interesting features" in the current Microsoft SQL Server implementation of the MERGE statement, but I still prefer it in most cases. My reasons are:
    1. MERGE is part of the SQL standard.
    2. Because MERGE is part of the standard, it is much more portable than work-arounds
    3. MERGE allows complex processing in a single statement, often reducing the total lines of code and code complexity by a factor of ten or more.
    4. MERGE processing is handled within the SQL engine, so it is inherently faster than equivalent user-written (Transact-SQL) code.

    While there are known limitations and other good reasons to use care with the MERGE statement, I prefer to use MERGE rather than to ignore it. I also vote for changes to support MERGE on the Connect web page to increase the visibility of community desire for improvements.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    4 MERGE processing is handled within the SQL engine, so it is inherently faster than equivalent user-written (Transact-SQL) code.
    There is a noticeable speed improvement of MERGE compared to UPDATE, in all UPDATE statements that I have converted to MERGE statements.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •