MERGE Bug with Updates Through a View

Demo

The script below creates two tables in a simplified one-to-one relationship. One table contains the compulsory columns for a logical data record; the other table holds the optional attributes. These two tables are combined by a view, forming one logical relation:

DROP VIEW IF EXISTS 
    dbo.Combo;
DROP TABLE IF EXISTS 
    dbo.Extended, 
    dbo.Base;
GO
CREATE TABLE dbo.Base 
(
    id bigint NOT NULL PRIMARY KEY
);

CREATE TABLE dbo.Extended
(
    id bigint NOT NULL PRIMARY KEY,
    opt1 integer NOT NULL,
    opt2 integer NOT NULL
);
GO
CREATE VIEW dbo.Combo
WITH SCHEMABINDING
AS
SELECT
    B.id, 
    E.opt1,
    E.opt2
FROM dbo.Base AS B
LEFT JOIN dbo.Extended AS E
    ON E.id = B.id;
GO

Inserting through the view

The view is updateable so long as we follow the rules.

Briefly stated, SQL Server must be able to identify the row(s) to change in exactly one table underlying the view.

Inserting a new base row through the view is no problem:

-- Could also use INSERT with NOT EXISTS
MERGE dbo.Combo WITH (SERIALIZABLE) AS C
USING (VALUES (1)) AS U (id)
    ON U.id = C.id
WHEN NOT MATCHED THEN
    INSERT (id) VALUES (U.id);

That statement succeeds, inserting a row into the Base table.

Updating through the view

Here’s an update to change the values of the optional columns:

UPDATE C
SET C.opt1 = U.opt1, 
    C.opt2 = U.opt2
FROM dbo.Combo AS C
JOIN (VALUES (1, 2, 3)) AS U (id, opt1, opt2)
    ON U.id = C.id;

This tries to update the Extended table values associated with the Base row with id 1. It doesn’t update any rows because we haven’t added any to the Extended table for this id.

Nevertheless, the point is the statement completes without error and reports:

(0 rows affected)

That may not be the result the statement author hoped for, but that’s a different story. Nothing bad or incorrect happened.

Plan

The execution plan shows how this works:

Update execution plan showing a Filter removing non-existent Extended rows

The Extended table projects an extra column named IsBaseRow1001. The Filter operator removes any rows where this column is NULL, meaning the outer join did not find a matching row.

Updating the view using MERGE

The same update attempt using a MERGE statement:

MERGE dbo.Combo AS C
USING (VALUES (1, 2, 3)) AS U (id, opt1, opt2)
    ON U.id = C.id
WHEN MATCHED THEN
    UPDATE SET 
        C.opt1 = U.opt1,
        C.opt2 = U.opt2;

This is where things go horribly wrong.

💥 Boom! 💥

You’ll get one of the following high-severity errors or retail assertions:

Msg 5180, Level 22, State 1
Could not open File Control Block (FCB) for invalid file ID 111
Verify the file location.
Execute DBCC CHECKDB.

Location:	 longrec.cpp:2605
Expression:	 accessor->GetSparseColumnSetUpdateBinding () == NULL || accessor->GetSparseColumnCount () == 0 || IS_ON(DBACCESSOR_SE_REPL, accessor->m_accMode)
Description:	 An update accessor cannot have explicit sparse column bindings and a column set binding.

Location:	 Sql\Ntdbms\storeng\drs\oledb\rowset.h:1269
Expression:	 hRow == reinterpret_cast(&m_hRowCurrent)
Msg 3624, Level 20, State 1
A system assertion check has failed.
Check the SQL Server error log for details.
Typically, an assertion failure is caused by a software bug or data corruption. 
To check for database corruption, consider running DBCC CHECKDB. 

You’ll also get a stack dump and your connection will be terminated.

The Problem

Unlike UPDATE, the MERGE thinks there’s a real row to update in the Extended table. It takes the fact that the WHEN MATCHED clause succeeded as a guarantee that the target row exists.

There is no row. The MATCH clause succeeds because the specified id value exists in the Base table. The target of the UPDATE clause, however, is the Extended table, which is connected via an outer join.

Querying the view shows NULL for all columns sourced from the Extended table. These NULL columns do not physically exist; they are provided by the outer join.

The MERGE execution plan does not feature an extra IsBaseRow1001 column and there is no Filter operator to remove rows that are only present due to the action of the outer join:

Faulty MERGE execution plan missing the Filter causing an update attempt on a non-existent row

As a result, the query processor attempts to update a row that does not exist, with unpredictable consequences.

If you are lucky, you will get one of the errors or assertions listed above. If you are unlucky, corruption could occur that might not be limited to the current database.

I have not encountered corruption yet in testing, but it is a dangerous theoretical possibility if a SQL Server retail assertion is not triggered from reading data at invalid (and essentially random) locations. Such corruption could occur in any memory locations, on physical pages associated with any database, or even internal tables crucial to the instance’s operation.

Final Thoughts

MERGE assumes there’s a row to update because one exists in the Base table, but the Extended table target is empty. The MERGE tries anyway and crashes. UPDATE performs the proper check before attempting any change and skips the faulty attempt gracefully and correctly.

Bug validated February 2025 on SQL Server 2008 to 2022 CU17 inclusive. All Editions are expected to be vulnerable, but I have only confirmed it on Developer.

On Azure SQL Database, I receive the following high-severity error and the connection is terminated:

Msg 64, Level 20, State 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

This strongly suggests Azure SQL Database is also affected, but different cloud handling means the assertion is not surfaced to the client and stack dumps are not available.

Azure Feedback Bug Report for your voting convenience.

Updating directly through a view (without an INSTEAD OF trigger) might not be a common pattern, but it is documented to work in SQL Server if the restrictions are followed, as is the case here.

Moreover, the fact the operation succeeds with UPDATE but not MERGE proves that SQL Server builds an invalid execution plan for the MERGE alternative. The important thing to look for is the IsBaseRow column and Filter operator.

Until this bug is fixed, a workaround is to not use MERGE to update through a view—use UPDATE instead, or employ an explicit INSTEAD OF trigger.

A suitable INSTEAD OF trigger for this demo would be:

CREATE TRIGGER dbo.Combo_InsteadOfUpdate
ON dbo.Combo
INSTEAD OF UPDATE
AS
SET XACT_ABORT, NOCOUNT ON;
SET ROWCOUNT 0;

-- Updates to Base columns omitted for brevity
-- Base id is assumed to be a stable key

UPDATE E
SET E.opt1 = I.opt1,
    E.opt2 = I.opt2
FROM Inserted AS I
JOIN dbo.Base AS B
    ON B.id = I.id
JOIN dbo.Extended AS E
    ON E.id = I.id;
GO

This trigger (an overhead only neccessary due to the bug) ensures updates are only attempted on Extended rows that exist when MERGE is used.

All operations (insert, update, delete, and merge) on the Base table are safe without the trigger because the Base table is the preserved table in the view, so rows will never be null-extended.

You might want to check your code base for examples of the MERGE view update pattern, as the consequences of hitting the bug could be very significant.

For a related, but less severe, issue concerning MERGE with a DELETE action through a view or common table expression, see this Database Administrators Q & A.

Thanks for reading.