Views

The SELECT clauses in a view definition cannot include the following:

·         An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

·         The INTO keyword

·         The OPTION clause

·         A reference to a temporary table or a table variable.

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. Any updates performed directly to a view's underlying tables are not verified against the view, even if CHECK OPTION is specified.

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.

ENCRYPTION          Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

A view can be created only in the current database. The CREATE VIEW must be the first statement in a query batch. A view can have a maximum of 1,024 columns.

When querying through a view, the Database Engine checks to make sure that all the database objects referenced anywhere in the statement exist and that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If the new table or view structure changes, the view must be dropped and re-created.