Assert Showplan Operator
The Assert operator verifies a condition. For example, it validates referential integrity or ensures that a scalar subquery returns one row. For each input row, the Assert operator evaluates the expression in the Argument column of the execution plan. If this expression evaluates to NULL, the row is passed through the Assert operator and the query execution continues. If this expression evaluates to a nonnull value, the appropriate error will be raised.
The Assert operator is a physical operator.
.gif "Assert operator icon")
Graphical execution plan icon
Examples
A. Validating a CHECK constraint
The following example updates the value in the Gender column for a specified employee ID in the Employee table. The table has a CHECK constraint that limits the values that are allowed in the column to the values 'F' and 'M'. The output of the query execution plan shows that the query optimizer uses the Assert operator to validate the value specified in the UPDATE statement against the CHECK constraint and raises an error when the conditions of the constraint are not met.
USE AdventureWorks;
GO
SET NOCOUNT ON;
GO
SET SHOWPLAN_ALL ON;
GO
UPDATE HumanResources.Employee
SET Gender = 'X' WHERE EmployeeID = 1;
GO
SET SHOWPLAN_ALL OFF;
GO
The execution plan output of the Assert operator appears below.
PhysicalOp
-----------------------------------------------------------------------
Assert
Argument
-----------------------------------------------------------------------
WHERE:(CASE WHEN upper([AdventureWorks].[HumanResources].[Employee].[Gender])<>N'F' AND
upper([AdventureWorks].[HumanResources].[Employee].[Gender])<>N'M' THEN
(0) ELSE NULL END)
B. Validating a FOREIGN KEY constraint
The following example deletes a row from the Person.Contact table. In this case, there are six tables that have a foreign key reference to the ContactID column in this table. The output of the query execution plan shows that the query optimizer uses the Assert operator to validate the DELETE statement against each of these constraints.
USE AdventureWorks;
GO
SET NOCOUNT ON;
go
SET SHOWPLAN_ALL ON;
GO
DELETE Person.Contact WHERE ContactID = 1209;
GO
SET SHOWPLAN_ALL OFF;
GO
The execution plan output of the Assert operator appears below.
PhysicalOp
-----------------------------------------------------------------------
Assert
Argument
-----------------------------------------------------------------------
WHERE:(CASE WHEN NOT [Expr1030] IS NULL THEN (0) ELSE CASE WHEN NOT
[Expr1031] IS NULL THEN (1) ELSE CASE WHEN NOT [Expr1032] IS NULL THEN
(2) ELSE CASE WHEN NOT [Expr1033] IS NULL THEN (3) ELSE CASE WHEN NOT
[Expr1034] IS NULL THEN (4) ELSE CASE WHEN NOT [Expr1035] IS NULL THEN (5) ELSE NULL END END END END END END)
C. Validating a scalar subquery
The following example uses a subquery in the WHERE clause of the query. This example intentionally uses a subquery that returns multiple rows to force the Assert operator to raise an error. The output of the query execution plan shows that the query optimizer uses the Assert operator to ensure that the subquery specified in the SELECT statement only returns a single row.
USE AdventureWorks;
GO
SET NOCOUNT ON;
go
SET SHOWPLAN_ALL ON;
GO
SELECT EmployeeID
FROM HumanResources.Employee
WHERE ContactID = (SELECT ContactID FROM Person.Contact);
GO
SET SHOWPLAN_ALL OFF;
GO
-- Run the query again to display the error message raised by the
-- Assert Operator.
SELECT EmployeeID
FROM HumanResources.Employee
WHERE ContactID = (SELECT ContactID FROM Person.Contact);
GO
The execution plan output of the Assert operator appears below.
PhysicalOp
-----------------------------------------------------------------------
Assert
Argument
-----------------------------------------------------------------------
WHERE:(CASE WHEN [Expr1007]>(1) THEN (0) ELSE NULL END)