MCTS 70-433 "WITH CHECK OPTION" of CREATE VIEW

There is quite a lot of complexity behind views, one basic concept the "WITH CHECK OPTION", is not allow the user/developer to change data in the underlying table which is outside the scope of the views "where clause" (this is difficult to phrase plainly?).

Another useful concept is SCHEMABINDING, here you can't drop an underlying table if it has SCHEMABINDING views built on it.

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 

[ WITH [ ,...n ] ] 

AS select_statement 

[ WITH CHECK OPTION ] [ ; ]

::= 

{

    [ ENCRYPTION ]

    [ SCHEMABINDING ]

    [ VIEW_METADATA ]     } 

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns.

VIEW_METADATA

Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata that the instance of SQL Server returns to these client-side APIs. This metadata enables the client-side APIs to implement updatable client-side cursors. Browse-mode metadata includes information about the base table that the columns in the result set belong to.

For views created with VIEW_METADATA, the browse-mode metadata returns the view name and not the base table names when it describes columns from the view in the result set.

When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers. For more information about updatable views, see Remarks.

CHECK OPTION

Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

http://msdn.microsoft.com/en-us/library/ms187956.aspx