WITH CHECK OPTION

This SHOWPLAN step indicates an existence test that is generated by the optimizer when modifying a view that involves one or more subqueries, and that was created with the WITH CHECK OPTION statement. If the existence test fails, the modification will also fail.

Assume the following view:

CREATE VIEW v1 AS
SELECT *
FROM mytable
WHERE col1 IN (SELECT col1 FROM mytable2)
WITH CHECK OPTION
  

The following example demonstrates the SHOWPLAN output for a query that updates a view containing subqueries that were created with the WITH CHECK OPTION.

Query:        UPDATE v1 SET col1 = 2
  
SHOWPLAN:    STEP 1
            The type of query is UPDATE
            The update mode is deferred
            FROM TABLE
            mytable
            Nested iteration
            Table Scan
            EXISTS : nested iteration
                FROM TABLE
                mytable2
                Nested iteration
                Table Scan
            TO TABLE
            mytable
            WITH CHECK OPTION
            EXISTS : nested iteration
                FROM TABLE
                mytable2
                Nested iteration
                Table Scan