Search results matching tag 'merge'

Halloween Protection – The Complete Series

Paul White — Wed, 20 Feb 2013 23:29:42 GMT

I have just published a four-part series for SQLPerformance.com 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 meaning to write about properly for years, ever since I read Craig Freedman’s 2008 blog post on the topic, which ended with the cryptic comment:

“…although I've used update statements for all of the examples in this post, some insert and delete statements also require Halloween protection, but I'll save that topic for a future post.”

That future post never materialized, sadly, so I thought I would have a go. The four parts of the series are summarized and linked below, I hope you find the material interesting.

Part 1 – The Halloween Problem and UPDATE statements

The SQL standard and three-phase separation
Logical update processing
Pipelined execution
The Halloween problem
Avoiding the problem in UPDATE statements

Part 2 – The Halloween Problem in INSERT and DELETE queries

INSERT examples
DELETE examples
Constraint checking and phase separation

Part 3 – Halloween Problem optimizations for MERGE

MERGE contains several optimizations the other DML statements do not
Hole-filling with merge join
Hole-filling with nested loops
Avoiding an extra B-tree navigation
Avoiding the join

Part 4 – The Halloween Problem and the Query Optimizer

Early optimization approaches
The SQL Server optimizer approach
The case of the redundant sort
HP levels and properties
Plan changes for Halloween Protection
Non-spool options
Row versioning
Heaps and forwarded records
T-SQL functions

As always, I appreciate your comments and feedback.

Paul White
@SQL_Kiwi
SQLkiwi@gmail.com

Obtaining rowcounts when using Composable DML [T-SQL]

jamiet — Thu, 07 Feb 2013 13:59:57 GMT

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 are replaced by an UPDATE (which I have talked about before in Using Composable DML to maintain entity history). Here’s the basic premise:

INSERT old_values( id, name ) --use Composable DML to store the values that were replaced by an UPDATE
SELECT	mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
;

This statement updates some rows in [tgt] and stores the old values in [old_values]. I think that’s rather useful, especially in a data warehousing scenario where one may wish to MERGE to a type 1 dimension table. Unfortunately this scenario gives rise to another limitation of Composable DML – the value returned by @@ROWCOUNT is the number of rows that were affected in [old_values], not in [tgt]. The following code (which you can simply copy/paste and execute and which is also available on pastebin) demonstrates this problem:

/******************************************************************************************************************************
A demonstration of capturing rowcounts when using composable DML. The problem I'm trying to demonstrate here is that 
I don't think there is a way to capture the number of rows affected by the MERGE
 
Jamie Thomson, 2013-02-07
******************************************************************************************************************************/
 
/*Setup table first and insert some data into [src]*/
USE tempdb
IF OBJECT_ID('src') IS NOT NULL		DROP TABLE src;
CREATE TABLE src (
	id		INT
,	name    NVARCHAR(MAX)
);
IF OBJECT_ID('tgt') IS NOT NULL		DROP TABLE tgt;
CREATE TABLE tgt (
	id		INT
,	name    NVARCHAR(MAX)
);
/*[updates] will be used as the target of the Composable DML insertion*/
IF OBJECT_ID('old_values') IS NOT NULL		DROP TABLE old_values;
CREATE TABLE old_values (
	id		INT
,	name    NVARCHAR(MAX)
);
INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
GO
 
/*Everything after here gets run twice because the batch ends with GO 2*/
INSERT old_values( id, name ) --use Composable DML to store the values that were replaced by an UPDATE
SELECT	mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
;
SELECT	[@@ROWCOUNT]=@@ROWCOUNT,row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)  -- <-Rowcount only provides tally of rows affected by the outer INSERT, not the MERGE
GO 2

Here is the output:

Notice that the Composable DML containing the MERGE statement is executed twice. The first execution inserts two rows into [tgt] yet @@ROWCOUNT returns zero because zero rows were inserted into [old_values] by the outer query. The second execution results in two rows in [tgt] being updated hence two rows are inserted into [old_values] and hence why @@ROWCOUNT returns two. It appears there is no way to discover the number of inserts or updates that were committed by the MERGE; if you’re a fan of logging rowcounts during ETL operations (which I think you should be) then this is a big problem. The only way I can think of getting around this problem is to break the statement into two like so (for brevity I haven’t included the full code listing so it is also available on pastebin):

/*Setup part is the same as before, we do need an extra table for capturing the output of our MERGE tho*/
IF OBJECT_ID('mrgout') IS NOT NULL		DROP TABLE mrgout;
CREATE TABLE mrgout (
	action_			NVARCHAR(MAX)
,	inserted_id		INT
,	inserted_name    NVARCHAR(MAX)
,	deleted_id		INT
,	deleted_name    NVARCHAR(MAX)
);
INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
GO
 
/*Everything after here gets run twice because the batch ends with GO 2*/
TRUNCATE TABLE mrgout;
INSERT mrgout(action_, inserted_id, inserted_name,deleted_id, deleted_name )
SELECT	mrgout.action_
,		mrgout.inserted_id
,		mrgout.inserted_name
,		mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
;
INSERT	dbo.old_values(id,name)
SELECT	deleted_id,deleted_name FROM mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
SELECT	[INSERT_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'INSERT'),[UPDATE_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'UPDATE'),row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)   GO 2

Executing that returns:

This is much better. We now know the tally of insertions and updates committed by the MERGE, unfortunately we have had to do it in two separate statements which in a way defeats the point of using MERGE in the first place (and don’t forget some of the other current problems with MERGE). If you can think of a better way of doing it then I’m all ears – please reply in the comments below.

I’m not saying don’t use MERGE and I’m not saying don’t use Composable DML; just be aware of their limitations. Personally I think there should be built-in functions, similar to @@ROWCOUNT, that return the number of rows INSERTed/DELETEd/UPDATEd by a MERGE; Aaron Bertrand agreed and raised a Connect submission to that affect: Katmai : Merge does not distinguish rowcounts in triggers which has, unfortunately, “been closed as won’t fix”.

@Jamiet

Incorrect Results with Indexed Views

Paul White — Tue, 05 Feb 2013 22:18:11 GMT

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 provide a reproduction script, but this blog entry does :)

The KB does say that reproducing the bug requires these features:

An indexed view on two tables that have a foreign key relationship
An update performed against the base tables
A query executed against the indexed view using a NOEXPAND hint

There are two important details I would like to add right away:

The NOEXPAND hint is not required to reproduce the bug on Enterprise Edition
The update must be performed by a MERGE statement

The fix is available in the following cumulative update packages:

Cumulative Update 2 for SQL Server 2012 SP1 [build 11.0.3339]
Cumulative Update 5 for SQL Server 2012 RTM [build 11.0.2395]
Cumulative Update 4 for SQL Server 2008 R2 SP2 [build 10.50.4270]
Cumulative Update 10 for SQL Server 2008 R2 SP1 [build 10.50.2868]
Cumulative Update 8 for SQL Server 2008 SP3 [build 10.00.5828]

No service pack contains this fix, you must apply one of the hotfix packages above.

Steps to Reproduce

The first thing we will need is two tables:

     CREATE TABLE dbo.Parent 

    (

        parent_id integer IDENTITY NOT NULL,

        value varchar(20) NOT NULL,

        CONSTRAINT PK_Parent_id 

            PRIMARY KEY CLUSTERED (parent_id)

    );

    GO

    CREATE TABLE dbo.Child

    (

        child_id integer IDENTITY NOT NULL,

        parent_id integer NOT NULL,

        CONSTRAINT PK_Child_id 

            PRIMARY KEY CLUSTERED (child_id)

    );

And a few rows of data:

    INSERT dbo.Child (parent_id)

    SELECT New.parent_id 

    FROM 

        (

        INSERT Parent 

        OUTPUT inserted.parent_id 

        VALUES 

            ('Apple'), 

            ('Banana'), 

            ('Cherry')

        ) AS New;

The tables now look like this (parent first):

We can now add the required FOREIGN KEY relationship:

    ALTER TABLE dbo.Child

    ADD CONSTRAINT FK_Child_Parent

    FOREIGN KEY (parent_id)

    REFERENCES dbo.Parent (parent_id);

Next, a very simple indexed view that joins the two tables (the view could contain more complex features like aggregates):

    CREATE VIEW dbo.ParentsAndChildren

    WITH SCHEMABINDING 

    AS

    SELECT 

        p.parent_id, 

        p.value, 

        c.child_id

    FROM dbo.Parent AS p

    JOIN dbo.Child AS c ON 

        c.parent_id = p.parent_id;

    GO

    CREATE UNIQUE CLUSTERED INDEX cuq 

    ON dbo.ParentsAndChildren (child_id);

The final step is to use a MERGE statement to make some changes to the Parent table:

    DECLARE @ParentMerge AS TABLE

    (

        parent_id integer PRIMARY KEY, 

        value varchar(20) NOT NULL

    );

    INSERT @ParentMerge

        (parent_id, value)

    VALUES

        (1, 'Kiwi Fruit'),

        (4, 'Dragon Fruit');

    MERGE dbo.Parent AS p

    USING @ParentMerge AS s ON 

        s.parent_id = p.parent_id

    WHEN MATCHED THEN 

        UPDATE SET value = s.value

    WHEN NOT MATCHED THEN 

        INSERT (value) VALUES (s.value)

    OUTPUT 

        $action, 

        inserted.parent_id, 

        deleted.value AS old_value, 

        inserted.value AS new_value;

This MERGE performs two actions:

Updates the value column of parent row 1 from Apple to Kiwi Fruit
Adds a new parent row 4 for Dragon Fruit

The statement includes an OUTPUT clause to show the changes it makes (this is not required for the repro):

This confirms that the changes have been made as we requested: parent row 1 has changed; and row 4 has been added. The changes are reflected in the base tables:

    SELECT * FROM dbo.Parent AS p;

    SELECT * FROM dbo.Child AS c;

As highlighted, row 1 has changed from Apple to Kiwi Fruit and row 4 has been added.

We do not expect to see row 4 in the indexed view because there are no child records for that row, and the indexed view uses an inner join. Checking the indexed view using the NOEXPAND table hint (required in non-Enterprise SKUs to use indexes on a view):

    SELECT *

    FROM dbo.ParentsAndChildren 

    WITH (NOEXPAND);

The results are incorrect. They show the old value of the data for parent row 1.

Now we try using the EXPAND VIEWS query hint to force SQL Server to access the base tables rather than reading view indexes:

    SELECT *

    FROM dbo.ParentsAndChildren

    OPTION (EXPAND VIEWS);

This query produces correct results.

On SQL Server Enterprise Edition, the optimizer chooses whether to access the indexed view or the base tables. For following query, without any hints, the optimizer chooses not to expand the view. It reads the view index and produces incorrect results:

    -- Enterprise Edition ONLY

    SELECT * 

    FROM dbo.ParentsAndChildren;

Perhaps adding a child row to match the new parent row 4 will somehow fix things up?

    INSERT dbo.Child (parent_id) VALUES (4);

    GO

    SELECT * FROM dbo.ParentsAndChildren WITH (NOEXPAND);

    SELECT * FROM dbo.ParentsAndChildren OPTION (EXPAND VIEWS);

No. The query plan that accesses the view index still returns an incorrect value for row 1. It seems MERGE has corrupted our indexed view.

Analysis using DBCC CHECKTABLE

Checking the view with DBCC CHECKTABLE returns no errors:

    DBCC CHECKTABLE (ParentsAndChildren);

Unless we use the EXTENDED_LOGICAL_CHECKS option:

    DBCC CHECKTABLE (ParentsAndChildren) WITH EXTENDED_LOGICAL_CHECKS;

The damage is repairable:

    ALTER DATABASE Sandpit 

    SET SINGLE_USER 

    WITH ROLLBACK IMMEDIATE;

    GO

    DBCC CHECKTABLE (ParentsAndChildren, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS;

    GO

    DBCC CHECKTABLE (ParentsAndChildren) WITH EXTENDED_LOGICAL_CHECKS;

    GO

    ALTER DATABASE Sandpit SET MULTI_USER;

You probably do not want to set your database to SINGLE_USER mode and run a DBCC repair after every MERGE statement, however. We could also rebuild the indexed view’s clustered index manually, of course.

Cause

For the MERGE statement above, the query optimizer builds a plan that does not update the indexed view (click to enlarge):

In a version of SQL Server with the fix applied, the same MERGE statement produces a plan that does maintain the indexed view:

The plan operators used to keep the view index in step with the base tables are highlighted. Without these operators, the changes to the base table are not correctly written to any indexes defined on the view. The root cause is related to the same simplification that allows the optimizer to remove the reference to the Parent table in this query:

    SELECT 

        COUNT_BIG(*)

    FROM dbo.Parent AS p 

    JOIN dbo.Child AS c ON 

        c.parent_id = p.parent_id;

The FOREIGN KEY relationship and NOT NULL constraints on the referencing column together mean that the join to Parent cannot affect the result of the query, so the join is simplified away. In SQL Server 2012, we can see when this simplification is performed because the following message appears when undocumented trace flags 8619 and 3604 are enabled during compilation:

The same message is emitted when a MERGE statement contains a WHEN MATCHED THEN UPDATE clause and either a WHEN NOT MATCHED THEN INSERT or WHEN MATCHED … THEN DELETE clause. These conditions combine such that the optimizer incorrectly concludes that a table reference can be removed, when in fact it is needed later on when the update side of the plan is built.

Other details of the query and database can affect whether the simplification can be misapplied. For example, if the FOREIGN KEY constraint contains an ON DELETE CASCADE clause, and the MERGE contains a DELETE clause, the simplification is not performed, the TF 8619 message does not appear, and the bug does not manifest.

The key to determining whether a particular query is vulnerable to this bug (TF 8619 aside) is to check whether the query plan includes operators to maintain the indexed view. At a minimum, you should see a update operator for the view:

SQL Sentry Plan Explorer identifies the operator as applying to a view explicitly, in SSMS you need to click on the graphical operator and inspect the Properties window.

Summary

The updated conditions for incorrect results are:

An indexed view that joins tables
Two of the tables have a single-column FOREIGN KEY constraint
A MERGE statement contains an UPDATE action that affects one of the tables
The MERGE statement also contains an INSERT or DELETE action (or both)
The optimizer applies a simplification that removes a table reference based on the FK relationship and other metadata
As a result, the MERGE execution plan does not contain the operators necessary to correctly maintain the indexed view
A subsequent query plan accesses an index on the view, either explicitly or via indexed-view matching (Enterprise Edition)

Note:

The simplification is not applied in tempdb
The simplification is not applied to multi-column foreign key constraints

Under these conditions, the indexes on the view do not reflect the state of the base tables and incorrect results are returned. Once the hot fix is applied, the optimizer does not misapply the simplification so the correct indexed view maintenance features are built into execution plans.

Update: I am adding this based on Ian Yates’ great question in the comments: my expectation is that applying this hotfix will not remove any existing indexed view corruption. You would need to test the hotfix as usual, apply it, and then either rebuild all affected indexed views manually, or run DBCC CHECKDB with the EXTENDED_LOGICAL_CHECKS option (which could take a long time).

Twitter: @SQL_Kiwi
Email: SQLKiwi@gmail.com

MERGE Bug with Filtered Indexes

Paul White — Sun, 09 Dec 2012 17:54:00 GMT

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 two tables, one that is the target of a MERGE statement, and another that contains updates to be applied to the target. The target table contains three columns, an integer primary key, a single character alternate key, and a status code column. A filtered unique index exists on the alternate key, but is only enforced where the status code is ‘a’:

     CREATE TABLE #Target 

    (

        pk          integer NOT NULL, 

        ak          character(1) NOT NULL,

        status_code character(1) NOT NULL,

        PRIMARY KEY (pk)

    );

    CREATE UNIQUE INDEX uq1

    ON #Target (ak)

    INCLUDE (status_code)

    WHERE status_code = 'a';

The changes table contains just an integer primary key (to identify the target row to change) and the new status code:

    CREATE TABLE #Changes

    (

        pk          integer NOT NULL,

        status_code character(1) NOT NULL,

        PRIMARY KEY (pk)

    );

Sample Data

The sample data for the example is:

    INSERT #Target 

        (pk, ak, status_code)

    VALUES 

        (1, 'A', 'a'),

        (2, 'B', 'a'),

        (3, 'C', 'a'),

        (4, 'A', 'd');

    INSERT #Changes

        (pk, status_code)

    VALUES

        (1, 'd'),

        (4, 'a');

         Target                     Changes
╔════╦════╦═════════════╗    ╔════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣
║ 1 ║ A ║ a           ║    ║ 1 ║ d           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ a           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝
║ 4 ║ A ║ d           ║
╚════╩════╩═════════════╝

The target table’s alternate key (ak) column is unique, for rows where status_code = ‘a’. Applying the changes to the target will change row 1 from status ‘a’ to status ‘d’, and row 4 from status ‘d’ to status ‘a’. The result of applying all the changes will still satisfy the filtered unique index, because the ‘A’ in row 1 will be deleted from the index and the ‘A’ in row 4 will be added.

Merge Test One

Let’s now execute a MERGE statement to apply the changes:

    MERGE #Target AS t

    USING #Changes AS c ON 

        c.pk = t.pk

    WHEN MATCHED 

        AND c.status_code <> t.status_code 

        THEN UPDATE SET status_code = c.status_code;

The MERGE changes the two target rows as expected. The updated target table now contains:

╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣
║ 1 ║ A ║ d           ║ <—changed from ‘a’
║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║
║ 4 ║ A ║ a           ║ <—changed from ‘d’
╚════╩════╩═════════════╝

Merge Test Two

Now let’s repopulate the changes table to reverse the updates we just performed:

    TRUNCATE TABLE #Changes;

    INSERT #Changes

        (pk, status_code)

    VALUES

        (1, 'a'),

        (4, 'd');

This will change row 1 back to status ‘a’ and row 4 back to status ‘d’. As a reminder, the current state of the tables is:

         Target                        Changes
╔════╦════╦═════════════╗    ╔════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣
║ 1 ║ A ║ d           ║    ║ 1 ║ a           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ d           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝
║ 4 ║ A ║ a           ║
╚════╩════╩═════════════╝

We execute the same MERGE statement:

    MERGE #Target AS t

    USING #Changes AS c ON 

        c.pk = t.pk

    WHEN MATCHED 

        AND c.status_code <> t.status_code 

        THEN UPDATE SET status_code = c.status_code;

However this time we receive the following message:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#Target' with unique index 'uq1'. The duplicate key value is (A).
The statement has been terminated.

Applying the changes using UPDATE

Let’s now rewrite the MERGE to use UPDATE instead:

    UPDATE t

    SET status_code = c.status_code

    FROM #Target AS t

    JOIN #Changes AS c ON

        t.pk = c.pk

    WHERE

        c.status_code <> t.status_code;

This query succeeds where the MERGE failed. The two rows are updated as expected:

╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣
║ 1 ║ A ║ a           ║ <—changed back to ‘a’
║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║
║ 4 ║ A ║ d           ║ <—changed back to ‘d’
╚════╩════╩═════════════╝

What went wrong with the MERGE?

In this test, the MERGE query execution happens to apply the changes in the order of the ‘pk’ column.

In test one, this was not a problem: row 1 is removed from the unique filtered index by changing status_code from ‘a’ to ‘d’ before row 4 is added. At no point does the table contain two rows where ak = ‘A’ and status_code = ‘a’.

In test two, however, the first change was to change row 1 from status ‘d’ to status ‘a’. This change means there would be two rows in the filtered unique index where ak = ‘A’ (both row 1 and row 4 meet the index filtering criteria ‘status_code = a’).

The storage engine does not allow the query processor to violate a unique key (unless IGNORE_DUP_KEY is ON, but that is a different story, and doesn’t apply to MERGE in any case). This strict rule applies regardless of the fact that if all changes were applied, there would be no unique key violation (row 4 would eventually be changed from ‘a’ to ‘d’, removing it from the filtered unique index, and resolving the key violation).

Why it went wrong

The query optimizer usually detects when this sort of temporary uniqueness violation could occur, and builds a plan that avoids the issue. I wrote about this a couple of years ago in my post Beware Sneaky Reads with Unique Indexes (you can read more about the details on pages 495-497 of Microsoft SQL Server 2008 Internals or in Craig Freedman’s blog post on maintaining unique indexes). To summarize though, the optimizer introduces Split, Filter, Sort, and Collapse operators into the query plan to:

Split each row update into delete followed by an inserts
Filter out rows that would not change the index (due to the filter on the index, or a non-updating update)
Sort the resulting stream by index key, with deletes before inserts
Collapse delete/insert pairs on the same index key back into an update

The effect of all this is that only net changes are applied to an index (as one or more insert, update, and/or delete operations). In this case, the net effect is a single update of the filtered unique index: changing the row for ak = ‘A’ from pk = 4 to pk = 1. In case that is less than 100% clear, let’s look at the operation in test two again:

         Target                     Changes                   Result
╔════╦════╦═════════════╗    ╔════╦═════════════╗    ╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║    ║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣    ╠════╬════╬═════════════╣
║ 1 ║ A ║ d           ║    ║ 1 ║ d           ║    ║ 1 ║ A ║ a           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ a           ║    ║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝    ║ 3 ║ C ║ a           ║
║ 4 ║ A ║ a           ║                            ║ 4 ║ A ║ d           ║
╚════╩════╩═════════════╝                            ╚════╩════╩═════════════╝

From the filtered index’s point of view (filtered for status_code = ‘a’ and shown in nonclustered index key order) the overall effect of the query is:

Before           After
╔════╦════╗    ╔════╦════╗
║ pk ║ ak ║    ║ pk ║ ak ║
╠════╬════╣    ╠════╬════╣
║ 4 ║ A ║    ║ 1 ║ A ║
║ 2 ║ B ║    ║ 2 ║ B ║
║ 3 ║ C ║    ║ 3 ║ C ║
╚════╩════╝    ╚════╩════╝

The single net change there is a change of pk from 4 to 1 for the nonclustered index entry ak = ‘A’. This is the magic performed by the split, sort, and collapse. Notice in particular how the original changes to the index key (on the ‘ak’ column) have been transformed into an update of a non-key column (pk is included in the nonclustered index). By not updating any nonclustered index keys, we are guaranteed to avoid transient key violations.

The Execution Plans

The estimated MERGE execution plan that produces the incorrect key-violation error looks like this (click to enlarge in a new window):

The successful UPDATE execution plan is (click to enlarge in a new window):

The MERGE execution plan is a narrow (per-row) update. The single Clustered Index Merge operator maintains both the clustered index and the filtered nonclustered index. The UPDATE plan is a wide (per-index) update. The clustered index is maintained first, then the Split, Filter, Sort, Collapse sequence is applied before the nonclustered index is separately maintained.

There is always a wide update plan for any query that modifies the database. The narrow form is a performance optimization where the number of rows is expected to be relatively small, and is not available for all operations. One of the operations that should disallow a narrow plan is maintaining a unique index where intermediate key violations could occur.

Workarounds

The MERGE can be made to work (producing a wide update plan with split, sort, and collapse) by:

Adding all columns referenced in the filtered index’s WHERE clause to the index key (INCLUDE is not sufficient); or
Executing the query with trace flag 8790 set e.g. OPTION (QUERYTRACEON 8790).

Undocumented trace flag 8790 forces a wide update plan for any data-changing query (remember that a wide update plan is always possible). Either change will produce a successfully-executing wide update plan for the MERGE that failed previously.

Conclusion

The optimizer fails to spot the possibility of transient unique key violations with MERGE under the conditions listed at the start of this post. It incorrectly chooses a narrow plan for the MERGE, which cannot provide the protection of a split/sort/collapse sequence for the nonclustered index maintenance.

The MERGE plan may fail at execution time depending on the order in which rows are processed, and the distribution of data in the database. Worse, a previously solid MERGE query may suddenly start to fail unpredictably if a filtered unique index is added to the merge target table at any point.

Connect bug filed here

Bug reproduced on the following SQL Server versions (all x64 Developer Edition):

2012 SP1 CUI (build 11.0.3321 – November 2012)
2008 R2 SP2 CU3 (build 10.50.4266 – October 2012)
2008 SP3 CU8 (build 10.0.5828 – November 2012)

Twitter: @SQL_Kiwi
Email: SQLkiwi@gmail.com

The current state of a MERGE Destination for SSIS

jamiet — Fri, 07 Dec 2012 11:18:45 GMT

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 component in the box; that is, when you install SSIS no MERGE Destination will be available. That being said the SSIS team have made available a MERGE destination component via Codeplex which you can get from http://sqlsrvintegrationsrv.codeplex.com/releases/view/19048.

I have never used it so cannot vouch for its usefulness although judging by some of the reviews you might not want to set your expectations too high. Your mileage may vary.

In the past it has occurred to me that a built-in way to provide MERGE from the SSIS pipeline would be highly valuable. I assume that this would have to be provided by the database into which you were merging hence in March 2010 I submitted the following two requests to Connect:

BULK MERGE (111 votes at the time of writing)
[SSIS] BULK MERGE Destination (15 votes)

If you think these would be useful feel free to vote them up and add a comment.

Lastly, this one is nothing to do with SSIS but if you want to perform a minimally logged MERGE using T-SQL Sunil Agarwal has explained how at Minimal logging and MERGE statement.

@Jamiet

Connect Digest : 2011-05-16

AaronBertrand — Mon, 16 May 2011 11:19:00 GMT

This week we're going to take a look at issues with filtered indexes. This feature was a very welcome addition in SQL Server 2008 and has enjoyed widespread usage if conversations on twitter and forums are any indication. But their implementation is not perfect - bugs in SSMS and numerous restrictions on their use have certainly reduced their impact, or at least made much more work for folks using them.

SSMS failures

Several people have reported various cases where Management Studio trips over filtered index functionality. I would only expect these bugs to be fixed in either 2008 R2 SSMS or Denali (in some cases both) - don't hold out for your 2005 or even 2008 versions of SSMS to be fixed for filtered index or other "new feature" issues.
#526911 : Table designer fails to correctly script filtered index

#462053 : The filter expression of a filtered index is lost when a table is modified by the Table Designer

#362699 : Table designer doesn't script WHERE clause in filtered indexes

#330238 : There is no way of creating filtered index from SSMS while designing a new table

#521333 : View Dependencies fails with Transact-SQL Error 515

Engine failures

Well, there is only one of these outstanding at this time, at least that I could find. I suspect this has to do with the testing matrix - they added both filtered indexes and MERGE in SQL Server 2008, but did not thoroughly test them *together.* There is a discussion thread on the MSDN forums about this specific issue:
#596086 : MERGE statement bug when INSERT/DELETE used and filtered index

Missing functionality

Some of these items are categorized as bugs, but I believe they simply represent scope creep. One of the items actually says something along the lines of, "It's not a bug; it's a known gap in functionality." Personally I think they got the bare minimum into SQL Server 2008 (supporting unique constraints the ANSI standard way, where more than one row can be NULL), and had no intention of supporting all of the other things that could make filtered indexes even more useful. If you find any of these suggestions valuable, please comment and vote - hopefully we can convince them to invest some time into this feature area for the next version after Denali!
#454744 : Filtered index not used and key lookup with no output

#341891 : Filtered index creation failed with NOT IN clause

#341737 : Filtered index does not allow filters on disjunctions

#648551 : filtered index on indexed view

#666238 : Support for more complex WHERE clause in filtered indexes

#518328 : Should be possible to create a filtered index on a deterministic persisted computed column

#498009 : Allow filtered unique index to be a canditate key for a foreign key

#329805 : Enhancements to the missing indexes DMV's for filtered indexes

Deprecate UPDATE FROM? Not if I can help it!

AaronBertrand — Mon, 24 Jan 2011 21:30:00 GMT

Fellow MVP Hugo Kornelis (blog) has suggested that the proprietary UPDATE FROM and DELETE FROM syntax, which has worked for several SQL Server versions, should be deprecated in favor of MERGE. Here is the Connect item he raised:

#332437 : Deprecate UPDATE FROM and DELETE FROM

As you can see, the response is quite divided (more so than any other item that I can recall) - at the time of writing, it was 11 up-votes and 12 down-votes. I have no shame in admitting that I am one of the people who down-voted Hugo's suggestion back when it was originally posted. And this blog post may seem like I'm replying quite late (almost three years after the fact), but the truth is that this debate was re-hashed recently due to a DELETE FROM issue that turned out to be a simple aliasing problem. But nonetheless, it got my gears spinning, so...

Why don't I give my love to MERGE?

The proprietary syntax works fine, in most cases. I find UPDATE FROM to be a very elegant way to update the contents of a table based on a join to one or more other tables. Let's say I have an Orders table and an OrderDetails table. Since each OrderDetails item may be shipped and handled (and even canceled) separately, there is a [Status] column in the OrderDetails table. And let's also say that we have discovered that Customer #1 would like to cancel all of his pending orders. There is no built-in cascade option that will cover this scenario, and we want to make sure that OrderDetails isn't inspected directly to see the (now out-of-date) status on any of this customer's line items. Before updating the parent row to 'Canceled', I would write an UPDATE FROM like this to affect all of the "child" rows:

UPDATE od
    SET od.[Status] = 'Canceled'
    FROM dbo.OrderDetails AS od
    INNER JOIN dbo.Orders AS o
    ON od.OrderID       = o.OrderID
    WHERE o.CustomerID  = @CustomerID
    AND o.[OrderStatus] = 'Pending';

ANSI (and --CELKO--, no doubt) would have me change it to this:

UPDATE dbo.OrderDetails
    SET [Status] = 'Canceled'
    WHERE EXISTS
    (
       SELECT *
         FROM dbo.Orders AS o
         WHERE o.OrderID   = dbo.OrderDetails.OrderID
         AND o.CustomerID  = @CustomerID
         AND o.OrderStatus = 'Pending'
    );

Or perhaps a CTE:

;WITH cte AS
(
    SELECT od.[Status]
      FROM dbo.Orders AS o
      INNER JOIN dbo.OrderDetails AS od
      ON o.OrderID       = od.OrderID
      WHERE o.CustomerID = @CustomerID
      AND o.OrderStatus  = 'Pending'
)
UPDATE cte SET [Status]  = 'Canceled';

And Hugo would apparently rather see this (and wait 20 minutes for me to figure out how to write it):

MERGE dbo.OrderDetails AS od
USING
(
    SELECT OrderID
      FROM dbo.Orders
      WHERE CustomerID = @CustomerID
      AND OrderStatus  = 'Pending'
) AS o
ON od.OrderID = o.OrderID
WHEN MATCHED THEN 
    UPDATE SET od.[Status] = 'Canceled';

While completely subjective, and this only represents how I feel at this exact moment in time, I would much rather write the first piece of code than ever see the latter three examples. The primary reason is that I can comment out the first two lines of the proprietary UPDATE FROM, and change the entire query to a SELECT if I want to check that my JOIN results are correct:

SELECT *
--UPDATE od
--    SET od.[Status] = 'Canceled'
    FROM dbo.OrderDetails AS od
    INNER JOIN dbo.Orders AS o
    ON od.OrderID       = o.OrderID
    WHERE o.CustomerID  = @CustomerID
    AND o.[OrderStatus] = 'Pending';

MERGE is not perfect, either. I will concede to Hugo that there are cases where you can write an UPDATE FROM statement that would lead to unpredictable results, but I will submit that you can render a lot of unpredictable results with ANSI-compliant syntax as well (I'll show one below). In the meantime, here are just a few *still active* Connect items (or that are closed as "Won't Fix") where users have discovered bugs in the way that MERGE has been implemented. Most importantly, that it has problems with filtered indexes, and causes unexpected @@ROWCOUNT results in triggers:

#620367 : MERGE fails to update with filtered index in place
#298395 : Katmai : Merge does not distinguish rowcounts in triggers
#476577 : MERGE can corrupt SCOPE_IDENTITY()
#340578 : Poor error message with MERGE when source/target appear in impossible places
#539084 : Search condition on a non-key column and an ORDER BY in source derived table breaks MERGE completely
#581548 : SQL2008 R2 Merge statement with only table variables fails
#582454 : [T-SQL] Proc with invalid MERGE statement gets compiled

And here are a few cautions from Books Online about MERGE that require a lot of parsing and may raise doubt about its use in your scenario:

CAUTION
It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.

CAUTION
Specifying READPAST with WHEN NOT MATCHED [ BY TARGET ] THEN INSERT may result in INSERT operations that violate UNIQUE constraints

I'll say all this a different way: the syntax is downright cumbersome. You've seen my above example, and no doubt the syntax diagram and examples from Books Online; it's not for the faint of heart. I took a quick poll on twitter and, while not pretending that it is a scientific result, all but one respondent admitted that to write a MERGE statement, they would have to look up the syntax in Books Online. And I was tempted to challenge the one respondent to a closed-book test. This is syntax that has been around for three years (and longer for those of us involved in the SQL Server 2008 beta), but is not easy to master AT ALL.

Shooting yourself in the foot - it's not just for UPDATE FROM

I'm all for deprecation, when warranted (TIMESTAMP or the use of sys.objects, anyone?). Look, Hugo is a very smart guy, and I respect him greatly, but his reasons for deprecating this syntax just don't hold up. He says that "improperly code join criteria You can shoot yourself in the foot with any standard syntax, too. A very common example is to not properly correlate the EXISTS clause, in which case ALL rows in OrderDetails are updated:

UPDATE dbo.OrderDetails
    SET [Status] = 'Canceled'
    WHERE EXISTS
    (
        SELECT *
          FROM dbo.Orders AS o
          WHERE o.OrderID   = o.OrderID -- bad correlation: no error, no warning!
          AND o.CustomerID  = @CustomerID
          AND o.OrderStatus = 'Pending'
    );

And you can do something quite similar with a CTE as well:

;WITH cte AS
(
    SELECT od.[Status]
      FROM dbo.Orders AS o
      INNER JOIN dbo.OrderDetails AS od
      ON o.OrderID       = o.OrderID -- bad correlation: no error, no warning!
      WHERE o.CustomerID = @CustomerID
      AND o.OrderStatus  = 'Pending'
)
UPDATE cte SET [Status] = 'Canceled';

A major benefit of MERGE, as Hugo points out, is that an error message is produced if you accidentally program the same bad correlation:

MERGE dbo.OrderDetails AS od
USING
(
    SELECT OrderID
      FROM dbo.Orders
      WHERE CustomerID = @CustomerID
      AND OrderStatus  = 'Pending'
) AS o
ON o.OrderID           = o.OrderID -- bad correlation
WHEN MATCHED THEN 
  UPDATE SET od.[Status] = 'Canceled';

Result:

Msg 8672, Level 16, State 1, Line 5
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

So, MERGE will better protect you from shooting yourself in the foot. But if you're not prone to shooting yourself in the first place...

Conclusion

Hugo also suggests simply removing this construct is not going to magically free up hours and hours of development time that will be better spent elsewhere. How much time does he really believe the development team is wasting on UPDATE FROM today?

I'm not vehemently opposed to MERGE. I think we should all become familiar with the syntax at some point, and in fact I've implemented it in a few places - but not as a replacement for UPDATE FROM, but rather to get rid of multi-statement "UPSERT" constructs like:

UPDATE ... ;
IF @@ROWCOUNT = 0
    INSERT ... ;

... or ...

IF EXISTS (...)
    UPDATE ... ;
ELSE
    INSERT ...;

To me, the "UPSERT" scenario is squarely in the wheelhouse of MERGE, as opposed to "UPDATE FROM." I just think that it should present the right benefits so that you'll want to use it on your own schedule, rather than when the dev team decides it's time to deprecate the method you're currently using to achieve the same result. Just be careful to use HOLDLOCK when using MERGE for more than one operation - for the guts on why, see this blog post from Dan Guzman.

If we're going to do anything with the way UPDATE FROM works, how about narrowing it down to something more specific and troublesome, such as the "quirky update" syntax? I think there would be a lot less opposition to having this kind of code raise an 'Incorrect syntax' error, and replace it with better windowed aggregate support:

CREATE TABLE dbo.OrderHistory
(
    OrderID      INT PRIMARY KEY,
    OrderTotal   DECIMAL(6,2) NOT NULL,
    RunningTotal DECIMAL(8,2) NULL
);
GO

INSERT dbo.OrderHistory(OrderID, OrderTotal)
          SELECT 1,   12.74
    UNION SELECT 2,  213.55
    UNION SELECT 3,   67.44;
GO

DECLARE @RunningTotal DECIMAL(8,2) = 0.00;

UPDATE dbo.OrderHistory
    SET @RunningTotal = RunningTotal = @RunningTotal + OrderTotal
    FROM dbo.OrderHistory;
GO

SELECT OrderID, OrderTotal, RunningTotal 
    FROM dbo.OrderHistory;
GO

DROP TABLE dbo.OrderHistory;
GO

Having these same running totals aggregated via an OVER() clause - now THAT is something where I can justify the dev team spending more time.

An Interesting MERGE Bug

Paul White — Tue, 03 Aug 2010 17:30:00 GMT

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 bug

We’ll look first at how MERGE can produce incorrect an output. We’ll need two tables: one that contains two rows of existing data; and a second that contains four rows of change information. The overall process looks like this:

This is a classic MERGE (or “upsert”) requirement. If the row already exists, the new value is added to it, otherwise a new row is inserted. Here’s the code to generate the sample tables and data:

DECLARE @Target
TABLE   (
        row_id  INTEGER NOT NULL PRIMARY KEY,
        value   INTEGER NOT NULL
        );
 
DECLARE @Delta
TABLE   (
        row_id  INTEGER NOT NULL PRIMARY KEY,
        delta   INTEGER NOT NULL
        );
 
-- Existing records (1 & 3)
INSERT  @Target
        (row_id, value)
VALUES  (1, 1),
        (3, 3);
 
-- Change table:
-- Updates rows 1 & 3
-- Inserts rows 2 & 4
INSERT  @Delta
        (row_id, delta)
VALUES  (1, 10),
        (2, 20),
        (3, 30),
        (4, 40);

We can easily write a MERGE query to perform the actions needed. Let’s also include an OUTPUT clause to show the effects on each row:

MERGE   @Target T
USING   @Delta D
        ON  D.row_id = T.row_id
WHEN    MATCHED THEN
        UPDATE SET T.value += D.delta
WHEN    NOT MATCHED BY TARGET THEN 
        INSERT VALUES (D.row_id, D.delta)
OUTPUT  D.row_id,
        $action,
        DELETED.value AS old_value,
        INSERTED.value AS new_value;

If we examine the @Target table after executing this query, we get exactly the results shown in the diagram. All good so far. The problem comes when we look at the rows produced by the OUTPUT clause:

When the action is ‘INSERT’, the new_value column contains an incorrect value. Instead of reporting the value actually inserted into the target table, it repeats the value associated with the last UPDATE operation. If we change the data so that multiple INSERTs are performed after the UPDATE, all the INSERTs get the same new_value associated with the prior UPDATE. The @Target table always contains the correct data – it’s just that the OUTPUT clause disagrees.

The Query Plan

The original Connect item states that this issue only affects table variables, but there’s more to see here. Let’s start by taking a look at the execution plan for the sample MERGE statement above (click to enlarge):

I have omitted several Compute Scalar iterators to make it easier to see what’s going on. There’s a scan of the @Delta table, followed by an Insert to the @Target table, and finally a Merge into the @Target table. If you are familiar with the sort of plans MERGE normally produces, you might find that arrangement a bit odd. Even if MERGE is new to you, you might think that an Insert followed by a Merge is surprising.

In a normal MERGE, we would expect to see an outer join between the source and target tables, followed by a Merge:

SQL Server performs an outer join so the Clustered Index Merge knows whether the current row already exists in the target table or not. If it exists, it is updated, otherwise a new row is inserted.

If you’ve been following my recent series on optimiser internals, you might suspect that the optimiser has applied a transformation to get the join-less plan, and the two plans are somehow logically identical. You would be right – the rule in question is LOJPrjGetToApply (Left Outer Join, Project Get to Apply).

The Optimisation

The idea behind the optimisation is to read a row from the @Delta table and immediately try to insert it into the @Target table. If that succeeds, the row didn’t already exist, and no further work is required.

If the row did exist, the PRIMARY KEY on @Target will cause a duplicate key violation to occur. The relational engine suppresses that error, and execution continues on down to the Clustered Index Merge iterator, which performs the necessary UPDATE.

This is a very useful (and perfectly safe) query optimisation if the conditions are right. There are rather a lot of conditions, the most important of which are:

The target of the merge must have a suitable clustered unique or primary key constraint
The changes (delta) table must have some sort of unique constraint (can be non-clustered) on the join column
The plan must be already using a nested loops join
The MERGE must include a NOT MATCHED condition plus at least one MATCHED clause
Equivalent columns in the two tables must have exactly the same type – no implicit conversions allowed
There can be no chance of a constraint violation – including NOT NULL constraints
The query must not update the clustered key

If any of the required conditions do not apply, the optimisation is not performed, and the query plan retains the outer join.

In order for the MERGE to return incorrect results:

The MERGE target must be a temporary table (the delta table can be anything)
The optimiser must produce a final plan that includes the transformation described above
The MERGE statement must include an OUTPUT clause that references the inserted pseudo table
The query has to perform an INSERT row action after an UPDATE (in clustered index order)

Importantly, it is only rows that result in an INSERT that cause problems. If only UPDATE or DELETE operations result from the MERGE, everything works perfectly – even with a table variable as the target.

Producing an Access Violation

The example data given for the @Delta table was carefully constructed to ensure that incorrect results were returned. With different sample data, we will get an Access Violation (AV) which terminates the connection and produces a stack dump on the SQL Server.

The simplest way (based on the test rig above) is to change the @Delta table contents to just include a single row that will result in an INSERT:

INSERT  @Delta
        (row_id, delta)
VALUES  (2, 2);

There is no row in @Target with row_id = 2, so this results in an INSERT.

To expand our test rig to encompass DELETE operations, we need to add an extra condition to the original MERGE statement. The new code will delete a record if its value is zero after applying the deltas:

MERGE   @Target T
USING   @Delta D
        ON  D.row_id = T.row_id
WHEN    MATCHED             -- New --
AND     T.value = -D.delta  -- New --
THEN    DELETE              -- New --
WHEN    MATCHED THEN
        UPDATE SET T.value += D.delta
WHEN    NOT MATCHED BY TARGET THEN 
        INSERT VALUES (D.row_id, D.delta)
OUTPUT  D.row_id,
        $action,
        DELETED.value AS old_value,
        INSERTED.value AS new_value;

With that modification in place, the following values in the @Delta table will update the row with row_id = 1, and delete the one with row_id = 3:

INSERT  @Delta
        (row_id, delta)
VALUES  (1, 10),
        (3, -3);

This combination works correctly because there is no INSERT operation. Any number of UPDATEs and DELETEs can be performed without issue, so long as no INSERTs occur.

Workarounds

The easiest workarounds involve preventing the query optimisation from happening in the first place. This is quite easy since there are so many conditions for it to apply. One way is to prevent the plan from using a nested loops join with an OPTION (HASH JOIN, MERGE JOIN) hint. There are many other alternatives in the same vein, of course.

A second workaround is to apply Trace Flag 8758 – unfortunately this disables a number of optimisations, not just the one above, so it’s not really recommended for long term use.

Third, we could turn off the LOJPrjGetToApply rule (again, see my previous optimiser posts) but that could also negatively affect other plans that benefit from the optimisation.

Final Thoughts

This isn’t a bug that will affect everyone, but it does show that your chances of turning up a bug increase as you push the limits of the optimiser and fairly new features like MERGE.

It will be interesting to see how the Connect item goes: will Microsoft fix this, or will they just say that MERGE was never intended for use with table variables? We’ll have to wait and see.

My thanks to ‘ALZDBA’ for reporting this problem. Personally, I see the prospect of returning incorrect data from the OUTPUT clause as more serious than the access violation. The OUTPUT clause is often used to record audit information, so producing incorrect data with no error seems undesirable. I would encourage you all to form your own view on this bug, and vote on it accordingly.

MERGE and OUTPUT – the swiss army knife of T-SQL

jamiet — Wed, 06 Jan 2010 18:46:22 GMT

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 a different use for it, returning values using an OUTPUT clause from a table used as the source of data for an insertion. In other words, if I’m inserting from [tableA] into [tableB] then I may want some values from [tableA] after the fact, particularly if [tableB] has an identity. Observe my first attempt using a straight insertion where I am trying to get a field from @source.[id] that is not used in the insertion:

DECLARE @source TABLE (

   [id] INT PRIMARY KEY

,  [name] VARCHAR(10)

);

INSERT @source VALUES(1000,'Harold'),(2000,'Madge');

DECLARE @destination TABLE (

   [id] INT PRIMARY KEY IDENTITY(1,1)

,  NAME VARCHAR(10)

);



INSERT @destination ([name])

OUTPUT INSERTED.[id] AS NEWID,[source].[id] AS OldId

SELECT [name]

FROM   @source;

This failed with the error:

The reason this fails is that the INSERT statement has no FROM clause thus the OUTPUT clause can never know about where the data is sourced from; the FROM clause in the SELECT that is used to source the data for the insertion is not actually part of the INSERT statement. Let’s try this with a MERGE instead:

DECLARE @source TABLE (

   [id] INT PRIMARY KEY

,  [name] VARCHAR(10)

);

INSERT @source VALUES(1000,'Harold'),(2000,'Madge');

DECLARE @destination TABLE (

   [id] INT PRIMARY KEY IDENTITY(1,1)

,  NAME VARCHAR(10)

);



MERGE  @destination

USING  (SELECT [id], [name] FROM @source) AS [source]

ON     (1=0) --arbitrary join condition

WHEN   NOT MATCHED THEN

       INSERT (name)

       VALUES  (source.Name)

       OUTPUT  INSERTED.id AS NEWID,[source].[id] AS OldId,INSERTED.name;

This worked!

Notice how we are able to reference the source of our data in the OUTPUT clause of of our MERGE statement which we couldn’t do when using INSERT. Pretty cool huh!

I remembered while writing this that Adam Machanic has already documented this capability in his blog post Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE and he has covered it in a lot more detail than I have here but still, I see no harm in reiterating and the code above is an easily executable demo of this cool little feature.

@Jamiet

N.B. Thanks to Aaron Bertrand for his instructions here on producing well-formatted T-SQL code for blogs! I’m putting this comment here so I know where to find it next time!

Extracting insert, update, delete rowcounts from T-SQL MERGE

jamiet — Sat, 29 Aug 2009 23:54:16 GMT

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 no built in functions for getting the counts for each DML operation (although Aaron Bertrand has a Connect submission asking for it).

There is an easy way around this however; MERGE includes a function called $action which can be used in the OUTPUT clause to specify whether a row was inserted, updated or deleted. I cut a small bit of code that demos how it works which you can get at the bottom of this blog post. Here’s what it produces:

I hope its useful to someone!

If you want to learn more about the MERGE statement and the OUTPUT clause then head over and read Adam Machanic’s post Dr OUTPUT: Or how I learned to stop worrying and love the MERGE.

@Jamiet

Here’s the code!! Disclaimer: Take it or leave it – just don’t shout at me if there are any problems here (although constructive comments are welcomed)

set nocount on;

create table #t
(
    id int
,    name varchar(max)
);
go

insert #t values (1,'Jim'),(2,'Sarah'),(3,'Hels');
go

DECLARE @rowcounts TABLE
(
    mergeAction nvarchar(10)
);
declare @insertCount int, @updateCount int, @deleteCount int;
merge into #t as tgt
using (    select 1 as id, 'James' as name
        union
        select 2, 'Sarah'
        union
        select 3, 'Helen'
        union
        select 4, 'Jack'
        union
        select 5, 'Annie') as src
on    tgt.id = src.id
when matched and tgt.name = src.name 
        THEN DELETE
when matched and tgt.name <> src.name
        THEN UPDATE SET tgt.name = src.name
when not matched
        THEN insert values (src.id, src.name)
OUTPUT $action into @rowcounts;

select    @insertcount=[INSERT]
,        @updatecount=[UPDATE]
,        @deletecount=[DELETE]
from    (
        select    mergeAction,1 rows
        from    @rowcounts
        )p
pivot
(
    count(rows)
FOR    mergeAction IN 
(    [INSERT], [UPDATE], [DELETE])
) as pvt
;
drop table #t;


print    '@insertcount = ' + cast(@insertcount as varchar);
print    '@updatecount = ' + cast(@updatecount as varchar);
print    '@deletecount = ' + cast(@deletecount as varchar);