Placing an ASSERT in a SQL Server query
Will this work reliably?


The answer to this depends on the strength of the guarantee you need. There have been bugs in the past around CASE evaluation-order, for example constant-folding an error-generating expression. There are no current bugs in SQL Server 2014 that I am aware of that would affect your query, but that does not mean they might not occur in future.

This is a risk with any code of course, but my assessment is that the risk is higher when combining this technique with MERGE. Merge has a complex implementation, and there have been many related bugs in the past, as you know.


  Is there a reason this should not be done?


Personally, I would look to avoid a divide-by-zero as part of the solution. A better option might be to attempt to write a value that violates an explicit CHECK (or some other) constraint on the target table. This would result in an explicit Assert operator in the execution plan, positioned after the Merge operator. It might not be desirable to add such a constraint (or even possible if the column is allowed to hold all values in its type domain) but it is one thing to consider.

One other general suggestion: avoid updating the target column when the values match. This is logically a no-op, but that doesn't always translate physically.

DECLARE @target AS TABLE (pk integer PRIMARY KEY, col2 integer NULL CHECK (col2 &lt;&gt; -1));
DECLARE @source AS TABLE (pk integer PRIMARY KEY, col2 integer NULL);

INSERT @target VALUES (1, 100), (2, NULL), (3, 300);
INSERT @source VALUES (1, 100), (2, 200), (3, 301);

MERGE @target AS T
USING @source AS S
    ON S.pk = T.pk
WHEN MATCHED 
    AND
    (
        -- No update if values match
        T.col2 IS NULL 
        OR T.col2 &lt;&gt; S.col2
    )
    THEN UPDATE SET col2 = 
        CASE 
            WHEN T.col2 IS NULL THEN S.col2
            ELSE -1 -- violates CHECK constraint
        END;