Views can be an effective tool for speeding up your selects and simplifying complex queries. Learn what indexed views are, where you might want to use them, how to create them, and what constraints exist with their use.
Views can be an effective tool for speeding up your selects
and simplifying complex queries. In this article, a special type of view
called an indexed view will be examined. We will take a look at what they
are, where you might want to use them, how they are created, and what constraints
exist with their use.
What Is an Indexed View?
Views allow you to create a virtual table by defining a
query against one or more tables. With a standard view, the result is not
stored in the database. Instead, the result set is determined at the time a
query utilizing that view is executed.
Creating a unique clustered index on a view changes it to an
indexed view. The clustered index is stored in SQL Server and updated like any
other clustered index, providing SQL Server with another place to look to potentially
optimize a query utilizing the indexed view.
Queries that dont specifically use the indexed view can even
benefit from the existence of the clustered index from the view. In the
developer and enterprise editions of SQL Server, the optimizer can use the
indexes of views to optimize queries that do not specify the indexed view. In
the other editions of SQL Server, however, the query must include the indexed
view and specify the hint NOEXPAND to get the benefit of the index on the view.
If your queries could benefit from having more than one
index on the view, non-clustered indexes can also be created on the view. This
would supply the optimizer with more possibilities to speed up the queries
referencing the columns included in the view.
Where to Use Them
Indexed views have both a benefit and a cost. The cost of
an indexed view is on the maintenance of the clustered index (and any
non-clustered indexes you may choose to add). One must weigh the cost to
maintain the index against the benefit of query optimization provided by the
index. When the underlying tables are subject to significant inserts, updates,
and deletes, be very careful in selecting the indexes (both table and view)
that will provide the greatest coverage across your queries for the lowest
Typically, environments that are best suited for indexed
views are data warehouses, data marts, OLAP databases, and the like.
Transactional environments are less suitable for indexed views. Look for repeating
joins utilizing the same columns, joins on large tables, aggregations on large
tables, and repeating queries as potential candidates for indexed views. Be
careful of creating indexed views where the result set contains more rows than
the base tables as this will be counterproductive.
How to Create Them
A view that is to be indexed has to be created with schema
binding. This means that once the indexed view is created, the underlying
tables cannot be altered in any way that would materially affect the indexed
view unless the view is first altered or dropped. It also means that all the
tables referenced in the view must be referenced by their two-part name
Below is an example of the CREATE statement for an indexed
view, MyView, and its underlying table, MyBigTable. The table is first
created, then the view that references two of the tables three columns, and
finally the unique clustered index on the view making it an indexed view.
CREATE TABLE MyBigTable(
ItemID INT PRIMARY KEY,
CREATE VIEW MyView WITH SCHEMABINDING AS
SELECT ItemID, QTY
WHERE QTY > 10
CREATE UNIQUE CLUSTERED INDEX idx_MyView ON MyView(QTY)
Once this index is created, the result set of this view is
stored in the database just like any other clustered index. Any query that explicitly
uses the view will be able to take advantage of the index on the view. Queries
that contain a predicate similar to the view and that fall into the range
defined by the view may also reap the optimization rewards of having that index
available (assuming the execution cost is non-trivial). Consider the following
WHERE QTY > 30
Even though the query does not use the indexed view, the
optimizer has the option of using the clustered index created on the view if it
provided better performance than the clustered or non-clustered indexes on the
If you want the optimizer to always choose the indexed view
over the base tables when optimizing a query containing an index view, you must
use the hint NOEXPAND. Conversely, if youd like to see how a query containing
an indexed view would perform utilizing the base tables instead, you can
specify the option EXPAND VIEWS, thus saving you the time substituting the base
An index cannot be created on just any view. Several
constraints exist that a view must meet in order for the index creation to be
successful. We discussed WITH SCHEMABINDING and two-part tablenames above.
Here are some other constraints.
The view must have been created with certain SET options, such as
QUOTED_IDENTIFIER and CONCAT_NULL_YIELDS_NULL set to ON.
The session creating the index must also have the correct SET
Any user-defined functions referenced by the view must have been
created using WITH SCHEMABINDING.
The view must be deterministic (consistently providing the same
result given the same input).
The base tables must have been created with the proper ANSI_NULLS
The result set of the view is physically stored in the database,
thus storage space for the clustered index is also a constraint to consider.
In addition to this, there are constraints on the contents
of the view. For instance, the view may not contain EXISTS or NOT EXISTS,
OUTER JOIN, COUNT(*), MIN, MAX, subqueries, table hints, TOP, UNION, and much
more. Check the SQL Server Development
Center on MSDN for a complete listing.
Indexed Views are a great way to realize performance gains
under the right circumstances. Be aware of the costs of creating indexed views
in highly transactional environments. If your environment happens to be one of
more querying than updating, however, indexed views might be just what the
SQL Server 2008 Administration in Action by Rod Colledge
MSDN SQL Server 2008 : Improving Performance with SQL Server 2008 Indexed Views
See All Articles by Columnist