THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » Merge   (RSS)
  • Halloween Protection – The Complete Series

    I have just published a four-part series for on the Halloween Problem. Some of you will never have heard of this issue, and those that have might associate it only with T-SQL UPDATE queries. In fact, the Halloween problem affects execution plans for INSERT, UPDATE, DELETE and MERGE statements. This is a topic I have been ...
    Posted to Page Free Space (Weblog) by Paul White on February 20, 2013
  • Obtaining rowcounts when using Composable DML [T-SQL]

    In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called Composable DML and also outlined one of its limitations; namely that data from the OUTPUT cannot be aggregated prior to insertion. Composable DML does have some useful scenarios however and one of those is in capturing and storing values that ...
    Posted to Jamie Thomson (Weblog) by jamiet on February 7, 2013
  • Incorrect Results with Indexed Views

    Summary: If you use MERGE, indexed views and foreign keys, your queries can return incorrect results. Microsoft have released a fix for incorrect results returned when querying an indexed view. The problem applies to: SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 The Knowledge Base article does not go into much detail, or ...
    Posted to Page Free Space (Weblog) by Paul White on February 5, 2013
  • MERGE Bug with Filtered Indexes

    A MERGE statement can fail, and incorrectly report a unique key violation when: The target table uses a unique filtered index; and No key column of the filtered index is updated; and A column from the filtering condition is updated; and Transient key violations are possible Example Tables Say we have ...
    Posted to Page Free Space (Weblog) by Paul White on December 9, 2012
  • The current state of a MERGE Destination for SSIS

    Hugo Tap asked me on Twitter earlier today whether or not there existed a SSIS Dataflow Destination component that enabled one to MERGE data into a table rather than INSERT it. Its a common request so I thought it might be useful to summarise the current state of play as regards a MERGE destination for SSIS. Firstly, there is no MERGE destination ...
    Posted to Jamie Thomson (Weblog) by jamiet on December 7, 2012
  • An Interesting MERGE Bug

    Summary: Investigating an optimiser transformation that exposes a bug in SQL Server’s MERGE implementation. I came across a Connect item today (by fellow SQL Server Central member ‘ALZDBA’) describing how using a combination of relatively new features can produce incorrect results or even an access violation inside SQL Server. Reproducing the ...
    Posted to Page Free Space (Weblog) by Paul White on August 3, 2010
  • MERGE and OUTPUT – the swiss army knife of T-SQL

    The new T-SQL MERGE statement in SQL Server 2008 seems to be finding many uses over and above its de facto UPSERT usage scenario, probably the most popular of which is its ability to update one table from another in an ANSI-compliant manner as detailed expertly by Hugo Kornelius in his blog post Let's deprecate UPDATE FROM! Today I stumbled upon ...
    Posted to Jamie Thomson (Weblog) by jamiet on January 6, 2010
  • Extracting insert, update, delete rowcounts from T-SQL MERGE

    Just lately I’ve been using T-SQL’s MERGE statement (introduced in SQL Server 2008) and one thing that I needed to do was extract rowcounts for each DML operation (i.e. INSERT, UPDATE, DELETE) conducted by a MERGE. I was surprised to find that while @@ROWCOUNT is supported for MERGE, it only returns the total number of affected rows and there are ...
    Posted to Jamie Thomson (Weblog) by jamiet on August 29, 2009
Privacy Statement