As part of a MERGE query that I want to run I'd like to assert at runtime that a certain condition holds. When a MERGE-match is found I'd like to update a certain column and have the following logic executed:

  1. If the target column is NULL, write the source value
  2. If the target is NOT NULL, assert that target and source are identical

I expect that the two values are always identical in case 2 but I might have made a mistake (have a bug). When that happens I'd like to crash the statement and let my app report the error. This is a very rare error condition, not something that can happen as part of usual processing.

So I was thinking that I could abuse a divide-by-zero exception to trigger a crash:

  TargetCol = CASE
    WHEN TargetCol IS NULL THEN SourceCol
    WHEN TargetCol = SourceCol THEN SourceCol
    ELSE 0/0 END --crash!

Will this work reliably? Is there a reason this should not be done?

  • I think RAISEERROR could be a good choice if you want to assert within a query. May 7, 2014 at 12:41
  • @BinayaRegmi can't call it as part of a query.
    – usr
    May 7, 2014 at 12:46
  • Why don't you want to use it in a block? May 7, 2014 at 12:51
  • 1
    Have you tried putting a CHECK constraint on the target OUTPUT table? That should blow up the statement.
    – Jon Seigel
    May 7, 2014 at 13:26
  • 1
    @JonSeigel I was referring to the dozen of merge bugs already found: mssqltips.com/sqlservertip/3074/… There are nasty corruption issues among them.
    – usr
    May 7, 2014 at 17:01

1 Answer 1


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 <> -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
        -- No update if values match
        T.col2 IS NULL 
        OR T.col2 <> S.col2
    THEN UPDATE SET col2 = 
            WHEN T.col2 IS NULL THEN S.col2
            ELSE -1 -- violates CHECK constraint

Merge plan with Assert

  • 1
    The nice thing about the CHECK tactic is that it is guaranteed to work. I'll take away this idea for future development. At the moment I'm more interested in having an extremely lightweight way to assert something (lightweight from a development standpoint). Reading your analysis I think the division trick is OK for non-critical functionality that does not kill people when it fails.
    – usr
    May 8, 2014 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.