Assert

Introduction

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 INSERT, UPDATE, DELETE, and MERGE statements to verify that no CHECK and 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:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan

Algorithm

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.

Evaluate Predicate

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 INSERT, UPDATE, DELETE, or MERGE statement, this also results in an immediate rollback of the current transaction.

In a 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).

Operator properties

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 *.

Property name	Description
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!

Implicit properties

This table below lists the behavior of the implicit properties for the Assert operator.

Property name	Description
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.