Working with Views

Views

Views can be used to define numerous combinations of rows and columns from one or more tables (see Chapter 7). When views use only a portion of the columns in a table, then the table data is referred to as being vertically filtered . When Views use only a portion of the rows in a table, then the table data is referred to as being horizontally filtered . Filtering data is a great advantage of using views. For example, if you need only a subset of data from a table, then a view will prevent users from accidentally (or purposely) seeing too much data, because the most that can be seen is what is contained in the view. The following list provides more information about views:

• A view is nothing more than a SELECT statement that has been given a name and saved in the database. The view does not contain the data, but defines where the data comes from. The view can often be treated like a table.

• Because a view is only a SELECT statement, you are able to filter the data that is returned to include some or all of a table's rows or columns. You are able to stick to the important data.

• The view's definition can consist of rows and columns from one or more tables in the current or other databases.

• A view can contain a maximum of 1,024 columns.

• Defaults, triggers, and rules are not allowed to be associated with a view. The only exception to this rule is the new INSTEAD OF trigger.

• A view can use data that may be anywhere (such as another database) as long as the view is created in current database.

• View names must follow the rules for identifiers.

• Views cannot be created using the ORDER BY clause unless they use the TOP clause (for example, TOP 100 PERCENT ).

• Views cannot be created using the COMPUTE BY or SELECT INTO clauses.

• View names must adhere to uniqueness rules. Two users may create a view name "MyView" because the object owner will make the fully qualified names unique.

• Views cannot be created on temporary tables.

• Temporary views cannot be created.

• Views can be nested up to 32 levels.

Partitioned Views

A partitioned view combines horizontally partitioned data from member tables across one or more servers (distributed partitioned views). A member table can be thought of as a segment from a larger table. There are two types of partitioned views in SQL Server: A local partitioned view and a distributed partitioned view. A local partitioned view is a partitioned view where all member tables reside on the local instance of SQL Server.

When creating partitioned views, there are some considerations that should be noted:

• Local Partitioned Views do not need to use CHECK constraints. Not using CHECK constraints will also provide the same results as with using a CHECK constraint, except that the Query Optimizer will have to perform a lengthy search against all member tables meeting the query search condition. Using Check constraints will reduce the cost of queries.

• When creating partitioned views, be sure that all columns of each member table are included in the partitioned view definition. Also, make sure that the same column is not referenced twice in the SELECT list. Make sure that all identical columns in all tables are of the same data type.

• When referencing member tables in a view, be sure to use the FROM clause to specifically declare a reference each table will be used.

• Be sure that Primary Keys are defined on the same column for each member table.

• It is possible to create updateable partitioned views. This requires that each table's SELECT statement refers to only one base table, the UNION ALL operator is used to join the resultsets together, and non-local tables use the full four-part identifier in their name.

Broken Ownership Chains

One problem that can arise when you are using views and other database objects occurs when you have different owners for objects in your database. Whenever there is a change in ownership, the owner of each object has to grant permissions to the object. When the ownership of objects in a chain are changed, there is a break in ownership or you have a broken ownership chain. To prevent ownership chains, have all objects created and owned by dbo.