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.

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)

See Also

Tasks

How to: Display an Actual Execution Plan

Concepts

Logical and Physical Operators Reference
Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	New content: Added the Examples section.