Tony Rogerson's ramblings on SQL Server

Ever wondered or even knew about the WITH CHECK OPTION on the CREATE VIEW clause? I’m going to show how the WITH CHECK OPTION works, show how, like the view it’s nothing but an inline substitution of SQL rather than anything special about the View. I will look at alternatives specifically Triggers.

Example Code

use tempdb

go

create table Customers (

    id  int not null identity constraint pk_Customers primary key clustered,

    customer_name   varchar(100) not null,

    county varchar(50) not null,

    status char(1) not null

)

go

insert Customers ( customer_name, county ) values( 'Trevor Dwyer', 'Hertfordshire' )

insert Customers ( customer_name, county ) values( 'Tony Rogerson', 'Hertfordshire' )

insert Customers ( customer_name, county ) values( 'Simon Sabin', 'Hertfordshire' )

insert Customers ( customer_name, county ) values( 'Mark Allison', 'Bedfordshire' )

insert Customers ( customer_name, county ) values( 'Peter Blackburn', 'Cambridgeshire' )

go

create view vw_Customers_Hertfordshire

    with schemabinding

as

    select id,

           customer_name,

           status,

           County

    from dbo.Customers

    where county = 'Hertfordshire'

    with check option

go

The WITH CHECK OPTION only works when you update, insert or delete using the view directly (this is a clue on how it’s been implemented).

An example call would be:

update vw_Customers_Hertfordshire

    set status = 'M'

The purpose of WITH CHECK OPTION is to make sure that your statement causing the data change does not result in any of the rows no longer appearing in the View when the view is referenced.

So, if we try and change the county from ‘Hertfordshire’ to ‘Cumbria’ for instance an error will result...

update vw_Customers_Hertfordshire

    set county = 'Cumbria'

go

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

Note that if we just update the base table directly then we get no error...

update Customers

    set county = 'Cumbria'

where county = 'Hertfordshire'

go

Works fine and updates the 3 rows.

From a referential integrity stance what is the point of that? It gives absolutely no data protection at all. I honestly cannot see the point of this option, probably the reason why I’ve never used it or come across anybody using it. Good practice is to use stored procedures and any specific Referential Integrity should be done using CHECK constraints, FOREIGN KEYs or TRIGGERS.

Anyway back to the point of the post; how does this work – is the view doing anything other than being expanded into the main query (just like an inline macro) before execution. Is the view do anything?

Here’s the execution plan of the UPDATE statement on its own (the equivalent statement for the View excluding the CHECK OPTION logic)...

update Customers

    set status = 'M'

where county = 'Hertfordshire'

go

  |--Clustered Index Update(OBJECT:([tempdb].[dbo].[Customers].[pk_Customers]), SET:([tempdb].[dbo].[Customers].[status] = RaiseIfNull(CONVERT_IMPLICIT(char(1),[@1],0))))

       |--Compute Scalar(DEFINE:([ConstExpr1008]=CONVERT_IMPLICIT(char(1),[@1],0)))

            |--Top(ROWCOUNT est 0)

                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customers].[pk_Customers]), WHERE:([tempdb].[dbo].[Customers].[county]=[@2]) ORDERED)

update vw_Customers_Hertfordshire

    set status = 'M'

go

  |--Assert(WHERE:(CASE WHEN [Expr1013] IS NULL THEN (0) ELSE NULL END))

       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tempdb].[dbo].[Customers].[county]), DEFINE:([Expr1013] = [PROBE VALUE]))

            |--Clustered Index Update(OBJECT:([tempdb].[dbo].[Customers].[pk_Customers]), SET:([tempdb].[dbo].[Customers].[status] = [Expr1003]))

            |    |--Compute Scalar(DEFINE:([Expr1003]='M'))

            |         |--Top(ROWCOUNT est 0)

            |              |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customers].[pk_Customers]), WHERE:([tempdb].[dbo].[Customers].[county]='Hertfordshire') ORDERED)

            |--Filter(WHERE:(STARTUP EXPR([tempdb].[dbo].[Customers].[county]='Hertfordshire')))

                 |--Constant Scan

I’ve colour coded the above query plan to distinguish between the update logic (in purple) and the with check option logic (in dark red).

As you can see there is no longer a concept of ‘the view’, in fact there is no longer any reference to it; the view and the check option have both been expanded into the main query.

The CHECK OPTION is a simple LEFT OUTER JOIN between the newly updated but yet to be committed value and the constant value held in the View – a nice trick but requires re-querying the data which leads to more IO and CPU.

This opens up a few more areas to cover and as this is already becoming a large article I’ll leave it for another day and entry; in that entry I’ll look more into how the WITH CHECK OPTION performs and works internally, I’ll give some scalability comparisons between other techniques – triggers (before and after) and constraints.