Sql-server – Placing an ASSERT in a SQL Server query

sql serversql server 2014

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:

MERGE
...
WHEN MATCHED BY TARGET THEN UPDATE SET
  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?

Best Answer

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

Merge plan with Assert