drop table  T100
go
drop view  VT100
go
Create table T100 (A int)
GO
Create view VT100 AS (SELECT * FROM T100 WHERE A < 2) with check option
GO
INSERT INTO T100 VALUES (1) -- Part of the view's result 
INSERT INTO T100 VALUES (2) 
GO
SELECT * FROM VT100 -- SHOWS 1
GO
INSERT INTO VT100 VALUES (-2) -- Works
GO
UPDATE VT100 SET A=5 -- Update fails because of WITH CHECK OPTION - GOOD
GO
INSERT INTO VT100 VALUES (999) -- Inser fails because of WITH CHECK OPTION - GOOD
GO
DELETE FROM VT100 -- DELETES ALL ROWS!!!! ******************* ???
go
print 'Select from T100'
SELECT * FROM T100  -- Still has one row

(1 row(s) affected)


(1 row(s) affected)

A           
----------- 
1

(1 row(s) affected)


(1 row(s) affected)

Server: 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.
Server: 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.

(2 row(s) affected)

Select from T100
A           
----------- 
2

(1 row(s) affected)

quote:

---

WITH CHECK OPTION clause forces all data modification statements executed against the view to adhere to the criteria set within the SELECT statement defining the view. If you use this clause, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed

---