Design views. This objective may include but is not limited to: common table expressions, partitioned views, WITH CHECK OPTION, WITH SCHEMABINDING
This section will show some of the options associated with views. It should be noted that we don’t see a section labeled for modifying data through a view, even though it is more than likely relevant.
Common Table Expressions (CTEs)
A common table expression is a named result set which can later be referenced within a select, insert, update, merge or delete statement. The result set of the CTE can be referenced multiple times within the following statement. It is also possible to use multiple CTEs within the statement. CTEs are allowed to reference themselves or previously defined CTEs. Those CTEs that are self-referencing provide the ability to perform a recursive query. See also, Using Common Table Expressions.
CTEs cannot contain the following clauses: COMPUTE or COMPUTE BY, ORDER BY (Except when used with TOP), INTO, OPTION (with hints), FOR XML, or FOR BROWSE.
CTEs can be used in a create view statement as part of the defining select statement. They can also be used in place of a view when the general use of the view is not required.
A partitioned view is a type of view which can be created such that multiple member tables underneath the view will hold a unique range of data based on the partitioning column(s) (Similar to a partitioned table) which must be enforced by a CHECK constraint constructed such that the ranges cannot overlap. The partitioned view which will then be created on top of these member tables will make the data appear as if it were coming from a single table. This can be accomplished via a local partitioned view, which stores all of the tables on the same instance as the view, or a Distributed partitioned view, which contains at least one of the tables on a remote server or instance. In the case of a local partitioned view, the architect should consider using a partitioned table instead. In the case of a distributed partitioned view, you would create the view on each member server or instance with the same name.
For distributed partitioned views there are a number of rules.
- Linked Servers set up on each of the servers or instances that map to the others where the member tables are stored.
- Lazy schema validation must be set so that the optimizer does not request meta data from an underlying table unless if it actually needs the data from that table.
- Tables located on a another server must be referenced with four part naming (servername.databasename.schemaname.tablename), an OPENROWSET function or the OPENDATASOURCE function.
- Each of the tables in the view cannot be referenced more than once in the query
- The member tables cannot have indexes created on any computed columns
- The tables must have a primary key, with at least one of the columns being the partitioning column(s)
- The primary key constraints must be based on the same number of columns
- The tables must have the same ANSI padding settings
- There can only be ONE constraint on the partition column
- Data can only be modified when it follows the rules below, and when the user has CONTROL, ALTER, TAKE OWNERSHIP or VIEW DEFINITION permissions on each of the underlying tables.
Modifying Data in a Partitioned View is only allowed under certain circumstances, such as the result set being a combination of individual select statements combined using a UNION ALL statement, each of which references an underlying member table. All columns must be included in the insert statement, and the DEFAULT keyword cannot be used. Inserts and updates cannot be performed on tables which has a TIMESTAMP column defined. Inserts, updates and deletes cannot be performed if there is a self-join with the same view or any of the member tables. Inserts cannot be performed if there is an IDENTITY column defined. Note that UPDATES allow for the use of an identity column; however, only the non-identity columns can be updated. Bulk inserting using BCP or the BULK INSERT statement is not allowed in partitioned views.
To get around these limitations, an INSTEAD OF trigger can be used to handle the logic necessary for the insert statement.
WITH CHECK OPTION
Since a view can be created in such a way that not all data present within the underlying tables is visible, the WITH CHECK OPTION can be set such that any data modification being applied to the table will result in the data remaining visible in the view. If it would fall out of range of the view, an error is returned.
When the WITH SCHEMABINDING option is set the underlying tables of the view cannot be dropped or altered in such a way that the changes would affect the view. To drop or alter the underlying tables, the view must be dropped prior to the tables being modified.
Note that when the WITH SCHEMABINDING option is set, two part naming must be adhered to (schemaname.tablename).