View Restrictions

When creating and using a view, these rules apply:

• CREATE VIEW statements cannot be combined with other SQL statements in a single batch.
• You cannot create a trigger on a view.
• The UNION operator cannot be used within a CREATE VIEW statement.
• Data modification statements (INSERT or UPDATE) are allowed on multitable views if the data modification statement affects only one base table. You cannot use data modification statements on more than one table in a single statement.
• INSERT statements are not allowed if a computed column exists within the view.
• INSERT statements are not accepted unless all the NOT NULL columns without defaults in the underlying table or view are included in the view through which you are inserting new rows. (SQL Server has no way to supply values for NOT NULL columns in the underlying table or view.)
• All column(s) being modified must adhere to all restrictions for the data modification statement as if it were executed directly against the base table. This applies to column nullability, constraints, identity columns, and columns with rules and/or defaults and base table triggers.
• UPDATE statements cannot change any column in a view that is a computation, nor can they change a view that includes aggregate functions, built-in functions, a GROUP BY clause, or DISTINCT.
• You cannot use READTEXT or WRITETEXT on text or image columns in views.
• By default, data modification statements on views are not checked to determine whether the rows affected will be within the scope of the view. You can issue an INSERT statement on a view to add a row to the underlying base table, but not to add it to the view. Similarly, you can issue an UPDATE statement that changes a row so that the row no longer meets the criteria for the view. If all modifications should be checked, use the WITH CHECK option.

Important If you define a view with an outer join and then query the view with a qualification on a column from the inner table of the outer join, the results can differ from what you expect. All rows from the inner table are returned. Rows that do not meet the qualification show a null value in the appropriate columns for those rows.