The Assert operator is used to verify one or more conditions that should never return True. If any of them do, execution of the query is stopped and an error message is triggered. Assert operators are typically found in execution plans for
MERGE statements to verify that no
FOREIGN KEY constraints were violated. They can also occur in
SELECT statements, though.
The Assert operator can only verify conditions based on the data in the current row.
Visual appearance in execution plans
Depending on the tool being used, an Assert operator is displayed in a graphical execution plan as shown below:
SQL Server Management Studio
Azure Data Studio
(version 17.4 and up)
(until version 17.3)
The basic algorithm for the Assert operator is as shown below:
Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.
The most important element of the Assert operator is the “Evaluate Predicate” action. Here the values in the current row are used to compute the result of an expression, stored in the Predicate property of the operator.
The exact logic in the Predicate property is determined by the optimizer. It is always an expression that returns an integer value , or NULL. The Predicate property is set such that NULL is returned when there are no error conditions. When an error condition does apply, the value returned determines which of the possible rules that this Assert verifies was violated.
Force error condition
If the Predicate of the Assert operator evaluates to a value other than NULL, the execution of the query is immediately halted. In an
MERGE statement, this also results in an immediate rollback of the current transaction.
SELECT statement, the transaction is not affected unless
SET XACT_ABORT is on. Any results that were already returned to the client are not undone; it is up to the client to recognize that the query did not finish normally and act accordingly.
A single Assert operator can be used to check for multiple possible error conditions. Which of the possible error conditions is raised is determined by the actual number returned, which is a zero-based index into an array of the errors checked. Unfortunately there is no way from the execution plan to immediately see which value corresponds to which possible error (usually the Predicate of the operator can be used to deduce the list of error checked).
The properties below are specific to the Assert operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Assert operator are marked with a *.
|Predicate||The expression that the operator applies to the values in each row to return either NULL, or a zero-based index into an (invisible in the plan) list of possible error conditions.|
|Startup Expression||This property has so far only been observed to be False. . I suspect that the only reason we see this property on Assert is that the XML schema for Showplan XML uses the same XSD element for the content of the Assert as it uses for Filter, where the Startup Expression does have a meaning.
If you ever encounter an execution plan that does have Startup Expression True on an Assert operator, please let me know!
This table below lists the behavior of the implicit properties for the Assert operator.
|Batch Mode enabled||The Assert operator supports row mode execution only.|
|Blocking||The Assert operator is non-blocking.|
|Memory requirement||The Assert operator does not have any special memory requirement.|
|Order-preserving||The Assert operator is fully order-preserving.|
|Parallelism aware||The Assert operator is not parallelism aware.|
|Segment aware||The Assert operator is not segment aware.|