Advantages
Views are used for several different reasons:
1.
To hide data complexity.
Instead of forcing your users to learn the T-SQL JOIN syntax you might wish to
provide a view that runs a commonly requested SQL statement.
2.
To protect the data. If
you have a table containing sensitive data in certain columns, you might wish
to hide those columns from certain groups of users. For instance, customer
names, addresses and their social security numbers might all be stored in the same
table; however, for lower level employees like shipping clerks, you can create
a view that only displays customer name and address. You can grant permissions
to a view without allowing users to query the underlying tables. There are a
couple of ways you might want to secure your data:
1.
Create a view to allow
reading of only certain columns from a table. A common example of this would be
the salary column in the employee table. You might not want all personnel to be
able to read manager's or each other's salary. This is referred to as
partitioning a table vertically and is accomplished by specifying only the
appropriate columns in the CREATE VIEW statement.
2.
Create a view to allow
reading only certain rows from a table. For instance, you might have a view for
department managers. This way, each manager can provide raises only to the
employees of his or her department. This is referred to as horizontal
partitioning and is accomplished by providing a WHERE clause in the SELECT
statement that creates a view.
Enforcing some simple
business rules. For example, if you wish to generate a list of customers that
need to receive the fall catalog, you can create a view of customers that have
previously bought your shirts during the fall.
Data exports with BCP. If
you are using BCP to export your SQL Server data into text files, you can
format the data through views since BCP's formatting ability is quite
limited.
Customizing data. If you
wish to display some computed values or column names formatted differently than
the base table columns, you can do so by creating views.
Disadvantages
Even though views can be a great tool for securing and customizing data, they can be slow. Indeed, they are not any faster than the query that defines them. With SQL Server 2000, indexed views (also referred to as "materialized" views) are supported to overcome this limitation.
Views can especially degrade the performance if they are based on other views. Therefore, it is recommended NOT to create views based on other views. All views should be created against base tables
From
the database management system (DBMS) perspective, a view is a description of
the data (a form of metadata). When a typical view is created, the metadata is
defined by encapsulating a SELECT statement that defines a result set to be
represented as a virtual table. When a view is referenced in the FROM clause of
another query, this metadata is retrieved from the system catalog and expanded
in place of the view's reference. After view expansion, the SQL Server query
optimizer compiles a single execution plan for executing the query. The query
optimizer searches though a set of possible execution plans for a query, and it
chooses the lowest-cost plan it can find, based on estimates of the actual time
it will take to execute each query plan.
In
the case of a nonindexed view, the portions of the view necessary to solve the
query are materialized at run time. Any computations such as joins or
aggregations are done during query execution for each query referencing the
view [Note1] . After a unique clustered index is created on the view, the
view's result set is materialized immediately and persisted in physical storage
in the database, saving the overhead of performing this costly operation at
execution time.
The
indexed view can be used in a query execution in two ways. The query can
reference the indexed view directly, or, more importantly, the query optimizer
can select the view if it determines that the view can be substituted for some
or all of the query in the lowest-cost query plan. In the second case, the
indexed view is used instead of the underlying tables and their ordinary
indexes. The view does not need to be referenced in the query for the query
optimizer to use it during query execution. This allows existing applications
to benefit from the newly created indexed views without changing those
applications.
Pros
Indexed
views can increase query performance in the following ways:
- Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
- Tables can be prejoined and the resulting data set stored.
- Combinations of joins or aggregations can be stored.
Secondary,
nonclustered indexes on views can provide additional query performance. Similar
to nonclustered indexes on tables, nonclustered indexes on views may provide
more options for the query optimizer to choose from during the compilation
process.
Applications
that benefit from the implementation of indexed views include:
- Decision support workloads.
- Data marts.
- Data warehouses.
- Online analytical processing (OLAP) stores and sources.
- Data mining workloads.
From
the query type and pattern point of view, the benefiting applications can be
characterized as those containing:
- Joins and aggregations of large tables.
- Repeated patterns of queries.
- Repeated aggregations on the same or overlapping sets of columns.
- Repeated joins of the same tables on the same keys.
- Combinations of the above.
Cons:
Adding
indexes to the schema increases the overhead on the database because the
indexes will require ongoing maintenance.
Not
all queries will benefit from indexed views. Similar to ordinary indexes, if
the indexed views are not used, there is no benefit. In this case, not only are
performance gains not realized, but the additional cost of disk space,
maintenance, and optimization is incurred. Online transaction processing (OLTP)
systems with many writes, or database applications with frequent updates, may
not be able to take advantage of indexed views because of the increased
maintenance cost associated with updating both the view and underlying base
tables.
Indexes
on tables and indexed views should be designed concurrently to obtain the best
results from each construct. Because both indexes and indexed views may be
useful for a given query, designing them separately can lead to redundant
recommendations that incur high storage and maintenance overhead. While you
tune the physical design of a database, tradeoffs must be made between the
performance requirements of a diverse set of queries and updates that the
database system must support. Therefore, identifying an appropriate physical
design for indexed views is a challenging task, and the Database Tuning Advisor
should be used wherever it is possible.
Query
optimization cost can increase substantially if there are many indexed views
that the query optimizer may consider for a particular query. A query optimizer
may consider all indexed views that are defined on any subset of tables in the
query. Each view has to be investigated for the potential substitution before
it is rejected. This may take some time, especially if there are hundreds of
such views for a given query.
Indexed View Background:
Views
are also known as virtual tables because the result set returned by the view
has the same general form as a table with columns and rows, and views can be
referenced just like tables in SQL statements. The result set of a standard
view is not stored permanently in the database. Every time a query references a
standard view, SQL Server substitutes the definition of the view into the query
internally until a modified query is formed that only references base tables
From
DBMS perspective, a view is a description of the data, a form of metadata. It
is possible to create a unique clustered index on a view, as well as
non-clustered indexes, to improve data access performance on the most complex
queries. In SQL Server 2000 and 2005, a view that has a unique clustered index
is referred to as an indexed view. The main benefit of using index view is that
the query optimizer searches though a set of possible execution plans for a
query, and chooses the lowest-cost plan it can find, based on estimates of the
actual time it will take to execute each query plan.
In
the case of a non-indexed view, the portions of the view necessary to solve the
query are materialized at run time. Any computations such as joins or
aggregations are done during query execution for each query referencing the
view. After a unique clustered index is created on the view, the view's result
set is materialized immediately and persisted in physical storage in the
database, saving the overhead of performing this costly operation at execution
time.
The
indexed view can be used in a query execution in two ways. The query can
reference the indexed view directly, or, more importantly, the query optimizer
can select the view if it determines that the view can be substituted for some
or the entire query in the lowest-cost query plan. In the second case, the
indexed view is used instead of the underlying tables and their ordinary
indexes. The view does not need to be referenced in the query for the query
optimizer to use it during query execution. This allows existing applications
to benefit from the newly created indexed views without changing those
applications.
Advantages of Indexed View:
Indexed
views improve the performance in the following ways:
- Joins and aggregations that process many rows.
- Join and aggregation operations that are frequently performed by many queries.
- Decision support workloads.
- The real benefits of Indexed Views are when we have aggregates that are too expensive to compute in real time.
- Aggregations can be pre-computed and stored in the index to minimize expensive computations during query execution.
- Tables can be pre-joined and the resulting data set stored.
- Combinations of joins or aggregations can be stored.
- Indexed views may be substituted by the query optimizer if it is determined that the cost of the query will be less than using the base table.
For
a standard view, the overhead of dynamically building the result set for each
query that references a view can be significant for views that involve complex
processing of large numbers of rows, such as aggregating lots of data, or
joining many rows. If such views are frequently referenced in queries, we can
improve performance by creating a unique clustered index on the view. When a
unique clustered index is created on a view, the result set is stored in the
database just like a table with a clustered index is stored.
Another
benefit of creating an index on a view is that the optimizer starts using the
view index in queries that do not directly name the view in the FROM clause.
Existing queries can benefit from the improved efficiency of retrieving data
from the indexed view without having to be recoded.
As
modifications are made to the data in the base tables, the data modifications
are reflected in the data stored in the indexed view. The requirement that the
clustered index of the view be unique improves the efficiency with which SQL
Server can find the rows in the index that are affected by any data
modification. The capability of the query optimizer to take advantage of
indexed views when it processes queries has improved over earlier versions when
both the query and the view definition contain the following matching elements:
Disadvantages of Indexed View:
To
use an indexed view in all other editions, the NOEXPAND table hint must be
used. Indexed views work best when the underlying data is infrequently updated.
The maintenance of an indexed view can be greater than the cost of maintaining
a table index. If the underlying data is updated frequently, the cost of
maintaining the indexed view data may outweigh the performance benefits of
using the indexed view.
Another
cost of indexed views is that the data is actually stored. By applying the
clustered index we are creating a copy of the data. So if we have several
indexed views on a single table, we will have several copies of the data, along
with the statistics of the data and the indexes and all of the overhead that
goes with it.
Following
are the disadvantages using index views that do not improve the performance:
- OLTP systems that have many writes to the disk.
- Databases that have many updates.
- Queries that do not involve aggregations or joins.
- High numbers of updates will most likely kill the benefit. If it is mainly reads then it will work fine.
- The overuse of indexed views can be like winding a watch with a wrench.
- Online index rebuilds are not supported for indexed views.
I
agree with Lutz that Filtered index is better and top of his comments on
filtered index versus indexed view; we can create our filtered index as a
non-unique index and where as indexed views can only be created as a unique
index. One point to keep in mind is we cannot create a filtered index on a
view. However, the query optimizer can benefit from a filtered index defined on
a table that is referenced in a view. With filtered index, online index
rebuilds is possible. We can rebuild filtered indexes while they are available
for queries. Online index rebuilds are not supported for indexed views.
When To Use A View
You need to
have a goal in mind when creating a view. There are a number of scenarios where
you will want to look for a view as a solution.
- To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
- To control access to rows and columns of data.
- To aggregate data for performance.
Let’s take a look at each of these
scenarios.
Complexity and Customization
Taking care of complex
joins and filtering rules inside of a view can benefit other users. As an
example, consider the following view from the Northwind database.
CREATE VIEW "Order Details Extended" AS
SELECT
"Order Details".OrderID,
"Order Details".ProductID,
Products.ProductName,
"Order Details".UnitPrice,
"Order Details".Quantity,
"Order Details".Discount,
(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
FROM
Products
INNER JOIN
"Order Details" ON
Products.ProductID = "Order Details".ProductID
A business user with an
ad-hoc reporting tool can take advantage of the above view in building
customized reports to support her goals. She can use the view to see all of the
details about an order without finding the tables to join for product and order
information, and without performing the calculation for the price discount. Not
only does this make the database easier for the end user, but it also allows a
DBA to make changes to the underlying tables without breaking end user reports.
View can also be useful
to novice SQL developers on your team. Although stored procedures can remove
complexity from a database only a view can be the target of an INSERT, UPATE,
or DELETE statement thus functioning more like a real table. There are
restrictions to modifying data through views. For more information, see “Modifying Data
Through a View” on MSDN. A view can
ensure the novice developer always accesses a set of tables in the most
efficient manner.
Take care not to let
the number of special case views explode in your database. Although it might be
tempting to provide every users a view to meet specific filtering requirements,
remember it is possible to SELECT from an existing view and provide additional
filtering. A large number of views can be difficult to maintain, especially
when breaking changes are required to the underlying schema.
Views As A Security Tool
SQL Server already has
the ability to restrict access to column in a table (see a previous article on OdeToCode for more information on this topic).
However, a view can be a useful tool to provide both column and row level
security for a database. Even though you can deny access to the salary column
of a payroll table to user Joe, Joe might be confused when he can see the
payroll table but has an error appear when his SELECT statement includes the
payroll column. A user-friendly solution is to deny Joe any access to the
payroll table, but give Joe access to a view that retrieves data from payroll
without salary information.
An even more
interesting scenario happens when Joe is a department manager and requires
access to the salary column, but only for those employees in his department.
Row level security is a perfect fit for a solution with a view. You can
construct a view to return only payroll records (with salary information) with
Joe’s user name in the row (see the built-in SQL Server function USER_NAME()).
Aggregate Views
Another great use case
for a view is if you need to roll up or aggregate data from a set of tables, as
in the following Northwind view.
CREATE VIEW "Sales by Category" AS
SELECT
Categories.CategoryID,
Categories.CategoryName,
Products.ProductName,
SUM("Order Details Extended".ExtendedPrice) AS ProductSales
FROM
Categories
INNER JOIN
(Products INNER JOIN
(Orders INNER JOIN "Order Details Extended" ON
Orders.OrderID = "Order Details Extended".OrderID)
ON Products.ProductID = "Order Details Extended".ProductID)
ON Categories.CategoryID = Products.CategoryID
GROUP BY
Categories.CategoryID,
Categories.CategoryName,
Products.ProductName
The view above will give us the sum of the total sales broken down by
category and product. If we wanted to see just the total sales for Chai, we
could use the following query.SELECT ProductSales FROM [Sales by Category] WHERE ProductName = 'Chai'
Queries performing aggregations can be harder to optimize than other
queries. Using can a view can not only hide the complexity but can also ensure
anyone who needs the aggregation will be using an optimized query.
