【Complete Showplan Operators】Chapter 1: Assert

Showplan operators are used by the Query Optimizer (QO) to build the query planin order to perform a specifed 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(adj. 绘成图画似的，绘画的;) execution plan by an icon.
I'm going to mention only of those that are more common: the frst being the Assert(vt. 声称，断言; 维护，坚持; 坚持自己的主张; 生效;).
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 specifes 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(adj. 一致的; 连续的; 不矛盾的; 坚持的;) 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

IF OBJECT_ID('Tab1') IS NOT NULL
DROP TABLE Tab1
GO
CREATE TABLE Tab1(ID Integer, Gender CHAR(1))
GO
ALTER TABLE TAB1 ADD CONSTRAINT ck_Gender_M_F CHECK(Gender IN('M','F'))
GO
INSERT INTO Tab1(ID, Gender) VALUES(1,'X')
GO

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(vt. 违反; 侵犯; 妨碍; 亵渎，强奸;) the Check Constraint. In this specifc case, the Assert applies the rule, "if the value is different to 'F' and different to 'M' then return 0 otherwise return 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:

ALTER TABLE Tab1 ADD ID_Genders INT
GO
IF OBJECT_ID('Tab2') IS NOT NULL
DROP TABLE Tab2
GO
CREATE TABLE Tab2(ID Integer PRIMARY KEY, Gender CHAR(1))
GO
INSERT INTO Tab2(ID, Gender) VALUES(1, 'F')
INSERT INTO Tab2(ID, Gender) VALUES(2, 'M')
INSERT INTO Tab2(ID, Gender) VALUES(3, 'N')
GO
ALTER TABLE Tab1 ADD CONSTRAINT fk_Tab2 FOREIGN KEY (ID_Genders) REFERENCES
Tab2(ID)
GO
INSERT INTO Tab1(ID, ID_Genders, Gender) VALUES(1, 4, 'X')

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.

|--Assert(WHERE:(CASE WHEN NOT [Pass1008] AND [Expr1007] IS NULL THEN (0) ELSE NULL
END))
|--Nested Loops(Left Semi Join, PASSTHRU:([Tab1].[ID_Genders] IS NULL), OUTER
REFERENCES:([Tab1].[ID_Genders]), DEFINE:([Expr1007] = [PROBE VALUE]))
|--Assert(WHERE:(CASE WHEN [Tab1].[Gender]<>'F' AND [Tab1].[Gender]<>'M'
THEN (0) ELSE NULL END))
| |--Clustered Index Insert(OBJECT:([Tab1].[PK]), SET:([Tab1].[ID] =
RaiseIfNullInsert([@1]),[Tab1].[ID_Genders] = [@2],[Tab1].[Gender] = [Expr1003]),
DEFINE:([Expr1003]=CONVERT_IMPLICIT(char(1),[@3],0)))
|--Clustered Index Seek(OBJECT:([Tab2].[PK]), SEEK:([Tab2].[ID]=[Tab1].
[ID_Genders]) ORDERED FORWARD)

Here we can see the Assert operator twice, frst (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(vt. 探索，调查; 用探针（或探测器等）探查，探测;) 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(adj. 标量的; 梯状的; 分等级的; 数量的;) 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:

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1), 'F')

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1), 'F')

|--Assert(WHERE:(CASE WHEN NOT [Pass1016] AND [Expr1015] IS NULL THEN (0) ELSE NULL
END))
|--Nested Loops(Left Semi Join, PASSTHRU:([tempdb].[dbo].[Tab1].[ID_
TipoSexo] IS NULL), OUTER REFERENCES:([tempdb].[dbo].[Tab1].[ID_TipoSexo]),
DEFINE:([Expr1015] = [PROBE VALUE]))
|--Assert(WHERE:([Expr1017]))
| |--Compute Scalar(DEFINE:([Expr1017]=CASE WHEN [tempdb].[dbo].
[Tab1].[Sexo]<>'F' AND [tempdb].[dbo].[Tab1].[Sexo]<>'M' THEN (0) ELSE NULL END))
| |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[Tab1].
[PK__Tab1__3214EC277097A3C8]), SET:([tempdb].[dbo].[Tab1].[ID_TipoSexo] =
[Expr1008],[tempdb].[dbo].[Tab1].[Sexo] = [Expr1009],[tempdb].[dbo].[Tab1].[ID] =
[Expr1003]))
| |--Top(TOP EXPRESSION:((1)))
| |--Compute Scalar(DEFINE:([Expr1008]=[Expr1014],
[Expr1009]='F'))
| |--Nested Loops(Left Outer Join)
| |--Compute Scalar(DEFINE:([Expr1003]=geti
dentity((1856985942),(2),NULL)))
| | |--Constant Scan
| |--Assert(WHERE:(CASE WHEN [Expr1013]>(1)
THEN (0) ELSE NULL END))
| |--Stream Aggregate(DEFINE:([Expr101
3]=Count(*), [Expr1014]=ANY([tempdb].[dbo].[Tab1].[ID_TipoSexo])))
| |--Clustered Index
Scan(OBJECT:([tempdb].[dbo].[Tab1].[PK__Tab1__3214EC277097A3C8]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Tab2].[PK__
Tab2__3214EC27755C58E5]), SEEK:([tempdb].[dbo].[Tab2].[ID]=[tempdb].[dbo].[Tab1].
[ID_TipoSexo]) ORDERED FORWARD)

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.

It's 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:

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1 WHERE ID =
1), 'F')
INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT TOP 1 ID_TipoSexo FROM Tab1),
'F')

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