Database Reference
In-Depth Information
SQL Server recognizes that it would be cheaper to scan the indexed view rather than perform the join between
two tables, and it generates the plan as shown in Figure
9-10
.
Figure 9-10.
Execution plan of the query (Enterprise or Developer editions)
In some cases, you can use such behavior if you need to optimize the systems where you cannot refactor the code
and queries. If you are working with Enterprise edition, you can create the indexed views and optimizer would start
using them for some of the queries, even when those queries do not reference the views directly. Obviously, you need
carefully consider the indexed view maintenance overhead that you would introduce with such an approach.
Partitioned Views
Partitioned views
combine the data via a
UNION ALL
of the multiple tables stored on the same or different database
servers. One of the common use-cases for such an implementation is data archiving; that is, when you move old
(historical) data to a separate table(s) and combine all of the data, current and historic, with the partitioned view.
Another case is
data sharding
, when you separate (shard) data between multiple servers based on some criteria. For
example, a large, Web-based shopping cart system can shard the data based on geographic locations of the customers.
In such cases, partitioned views can combine the data from all shards and use it for analysis and reporting purposes.
■
Note
we will discuss partitioned views in greater detail in Chapter 15, “Data partitioning.”
Updatable Views
Client applications can modify data in underlying tables through a view. It can reference the view in the DML
statements, although there is a set of requirements to be met. To name just a few, all modifications must reference
the columns from only one base table. Those columns should be physical columns and should not participate in the
calculations and aggregations.
■
You can see the full list of requirements in Books Online at:
Note
These restrictions are the biggest downside of this approach. One of the reasons we are using views is to add
another layer of abstraction that hides the implementation details. By doing updates directly against views, we are
limited in how we can refactor them. If our changes violate some of the requirements to make the view updatable,
the DML statements issued by the client applications would fail.
Another way to make a view updateable is by defining an
INSTEAD OF
trigger. While this gives us the flexibility to
refactor the views in the manner we want, this approach is usually slower than directly updating the underlying tables.
It also makes the system harder to support and maintain—you must remember that data in tables can be modified
through views.
