273
Chapter 11: Projecting Data Through Views
11
multiple servers within a view. This is one case in which basing several reports,
and even stored procedures, on a single view improves the stability, integrity, and
maintainability of the system.
■ Typically views are created for specifi c purposes or for a specifi c group or depart-
ment within an organization. To group the views together in an attempt to ease
maintainability, you may assign them to a specifi c schema. For example, you have a
set of views that have been created specifi cally for reporting. Those views could all
be included as part of a Reporting Schema.Using Views for Column-Level Security
One of the basic relational operators is projection—the capability to expose specifi c columns. One
primary advantage of views is their natural capacity to project a predefi ned set of columns. Here is where
theory becomes practical. A view can project columns on a need-to-know basis and hide sensitive,
irrelevant, or confusing columns for the purpose of the view (for example, payroll and credit card data).SQL Server supports column-level security, and it’s a powerful feature. The problem is that ad hoc
queries made by users who don’t understand the schema well often run into security errors. You
should implement SQL Server column-level security, and then also use views to shield users from ever
encountering the security. Grant users read permission from only the views, and restrict access to the
physical tables (see Chapter 33, “Authorizing Securables”).Some databases use only views for column-level security without any SQL Server–enforced security.
This is woefully inadequate and will surely be penalized by any serious security audit.The goal when developing views is two-fold: to enable users to get to the data easily and to
protect the data from the users. By building views that provide the correct data, you can
prevent erroneous or inaccurate queries and misinterpretation.Other advanced forms of views exist.
Distributed partition views, or federated databases, divide large tables across multiple smaller tables or separate
servers to improve performance. The partitioned view then spans the multiple tables or servers, thus sharing the
query load across more disk spindles. (See Chapter 49, “Partitioning.”)
Indexed views are a powerful feature that actually materializes the data, storing the results of the view in a clustered
index on disk, so in this sense it’s not a pure view. Like any view, it can select data from multiple data sources. Think of
the indexed view as a covering index but with greater control — you can include data from multiple data sources, and
you don’t need to include the clustered index keys. The index may then be referenced when executing queries, regard-
less of whether the view is in the query, so the name is slightly confusing. (See Chapter 45, “Indexing Strategies.”)Because designing an indexed view is more like designing an indexing structure than creating a view,
Chapter 45, “Indexing Strategies,” includes indexed views.c11.indd 273c11.indd 273 7/30/2012 4:41:07 PM7/30/2012 4:41:07 PM
http://www.it-ebooks.info