Showplan Operator of the week – Assert

Comments 0

Share to social media

Well my friends, I was wondering how to help you to understand execution plans in a practical way. So I decided that I’d talk about the Showplan Operators.

Showplan Operators are used by the Query Optimizer (QO) to build the query plan in order to perform a specified operation. A query plan will consist of many physical operators. The Query Optimizer uses a simple language that represents each physical operation by an operator, and each operator is represented in the graphical execution plan by an icon.

I’ll try to talk about one operator every week, but so as to avoid having to continue to write about these operators for years, I’ll mention only of those that are more common: The first being the Assert.

The Assert is used to verify a certain condition, it validates a Constraint on every row to ensure that the condition was met. If, for example, our DDL includes a check constraint which specifies only two valid values for a column, the Assert will, for every row, validate the value passed to the column to ensure that input is consistent with the check constraint.

Assert  and Check Constraints:

Let’s see where the SQL Server uses that information in practice. Take the following T-SQL:

To the command above the SQL Server has generated the following execution plan:


As we can see, the execution plan uses the Assert operator to check that the inserted value doesn’t violate the Check Constraint. In this specific case, the Assert applies the rule, ‘if the value is different to “F” and different to “M” than return 0 otherwise returns NULL‘.

The Assert operator is programmed to show an error if the returned value is not NULL; in other words, the returned value is not a “M” or “F”.

Assert checking Foreign Keys

Now let’s take a look at an example where the Assert is used to validate a foreign key constraint. Suppose we have this  query:


Let’s look at the text execution plan to see what these Assert operators were doing. To see the text execution plan just execute SET SHOWPLAN_TEXT ON before run the insert command.

Here we can see the Assert operator twice, first (looking down to up in the text plan and the right to left in the graphical plan) validating the Check Constraint. The same concept showed above is used, if the exit value is “0” than keep running the query, but if NULL is returned shows an exception.

The second Assert is validating the result of the Tab1 and Tab2 join. It is interesting to see the “[Expr1007] IS NULL“. To understand that you need to know what this Expr1007 is, look at the Probe Value (green text) in the text plan and you will see that it is the result of the join. If the value passed to the INSERT at the column ID_Gender exists in the table Tab2, then that probe will return the join value; otherwise it will return NULL. So the Assert is checking the value of the search at the Tab2; if the value that is passed to the INSERT is not found  then Assert will show one exception.

If the value passed to the column ID_Genders is NULL than the SQL can’t show a exception, in that case it returns “0” and keeps running the query.

If you run the INSERT above, the SQL will show an exception because of the “X” value, but if you change the “X” to “F” and run again, it will show an exception because of the value “4”. If you change the value “4” to NULL, 1, 2 or 3 the insert will be executed without any error.

Assert checking a SubQuery:

The Assert operator is also used to check one subquery. As we know, one scalar subquery can’t validly return more than one value: Sometimes, however, a  mistake happens, and a subquery attempts to return more than one value . Here the Assert comes into play by validating the condition that a scalar subquery returns just one value.

Take the following query:

You can see from this text showplan that SQL Server as generated a Stream Aggregate to count how many rows the SubQuery will return, This value is then passed to the Assert which then does its job by checking its validity.

Is very interesting to see that  the Query Optimizer is smart enough be able to avoid using assert operators when they are not necessary. For instance:

For both these INSERTs, the Query Optimiser is smart enough to know that only one row will ever be returned, so there is no need to use the Assert.

Well, that’s all folks, I see you next week with more “Operators”.

About the author

Fabiano Amorim

See Profile

Fabiano Amorim is a Data Platform MVP since 2011 that loves to conquer complex, challenging problems—especially ones that others aren’t able to solve. He first became interested in technology when his older brother would bring him to his work meetings at the age of 14. With over a decade of experience, Fabiano is well known in the database community for his performance tuning abilities. When he isn’t working, he loves to read and spend time with his family.

Fabiano Amorim's contributions