You establish indexed views by creating a unique
clustered index on the view itself, independent of the member tables
that it references. The creation of this unique index transforms a view
from an object that is virtual in nature to one that has physical
storage associated with it. Like all other indexes, the index on a view
takes up physical storage, requires maintenance, and, most importantly, can provide performance benefits that justify its creation.
Creating Indexed Views
Indexed views were
first available for creation in SQL Server 2000 and continue to be a
viable means for improving query performance in SQL Server 2008. An
index can be created on a view in all versions of SQL Server 2008, but
there are limitations on some of the versions. The Developer and
Enterprise Editions of SQL Server 2008 are the only editions that
support the use of indexed views for queries that don’t specifically
reference the views. Other editions of SQL Server must reference the
view by name in the SQL statements and must also use the NOEXPAND keyword in the query.
Regardless of the edition
of SQL Server you are running, some basic requirements must be satisfied
for you to create an indexed view. These requirements, which follow,
are detailed in SQL Server 2008 Books Online:
The ANSI_NULLS and QUOTED_IDENTIFIER options must be set to ON when the CREATE VIEW statement is executed.
The ANSI_NULLS option must be set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
The view must not reference any other views, only base tables.
All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.
User-defined functions referenced in the view must be created with the SCHEMABINDING option.
Tables
and user-defined functions must be referenced via two-part names in the
view. One-part, three-part, and four-part names are not allowed.
All functions referenced by expressions in the view must be deterministic.
If the view definition uses an aggregate function, the SELECT list must also include COUNT_BIG (*).
The DATA ACCESS property of a user-defined function must be NO SQL, and the EXTERNAL ACCESS property must be NO.
CLR functions can appear only in the SELECT list of the view and can reference only fields that are not part of the clustered index key. They cannot appear in the WHERE clause of the view or the ON clause of a JOIN operation in the view.
CLR functions and methods of CLR user-defined types used in the view definition must have the properties set as DETERMINISTIC = TRUE, PRECISE = TRUE, DATA ACCESS = NO SQL, and EXTERNAL ACCESS = NO.
If GROUP BY is specified, the view SELECT list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.
The view cannot contain any of the T-SQL elements shown in the following list:
| * or tablename.* | An expression on a column found in the GROUP BY clause | A derived table |
| A common table expression (CTE) | A rowset function | The UNION, EXCEPT, or INTERSECT operators |
| Subqueries | Outer joins or self-joins | The TOP clause |
| The ORDER BY clause | The DISTINCT keyword | COUNT (COUNT_BIG is allowed) |
| AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP | A SUM function that references a nullable expression | A CLR user-defined aggregate function |
| The full text predicate | COMPUTE or COMPUTE BY CONTAINS or FREETEXT | CROSS APPLY or OUTER APPLY operators |
| Table hints | Join hints | |
You can see from this list
that the number of requirements is extensive. It can therefore be
difficult to determine whether all the requirements have been met for a
particular view. To simplify this determination, you can query the IsIndexable property, using the OBJECTPROPERTY function. The following example demonstrates the use of the IsIndexable property against the sys.views catalog view:
SELECT name AS ViewName
,SCHEMA_NAME(schema_id) AS SchemaName
,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
,create_date
,modify_date
FROM sys.views;
The IsIndexable property returns a 1 (or TRUE) if an index can be created on the view and a 0 if it is not indexable. Most of the views in the Adventureworks2008CREATEvProductAndDescription view. The SET options required when creating the index are included in the example as well:
database are not indexable, but the database does contain a couple of
examples of views that have been indexed. The following example shows
the statement for an index on the
SET ARITHABORT ON — for 80 compatibility or earlier
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX [IX_vProductAndDescription]
ON [Production].[vProductAndDescription]
(
[CultureID] ASC,
[ProductID] ASC
)
The following example shows the Production.vProductAndDescript view that the index was created on:
CREATE VIEW [Production].[vProductAndDescription]
WITH SCHEMABINDING
AS
View (indexed or standard) to display products
—and product descriptions by language.
SELECT
p.[ProductID]
,p.[Name]
,pm.[Name] AS [ProductModel]
,pmx.[CultureID]
,pd.[Description]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductModel] pm
ON p.[ProductModelID] = pm.[ProductModelID]
INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx
ON pm.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [Production].[ProductDescription] pd
ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
Indexed Views and Performance
Adding indexes to tables is a
generally accepted means for improving database performance. Indexes
provide a keyed lookup to rows of data that can improve database access
and avoid the performance nightmare of a table scan where the entire
contents of a table are searched. The same basic principles apply to
indexes on views, but indexed views are best utilized to increase
performance in the following scenarios:
Aggregations such as SUM or AVG can be precomputed and stored in the index to minimize the potentially expensive computations during query execution.
Large table joins can be persisted to eliminate the need to write a join when retrieving the data.
A combination of aggregations and large table joins can be stored.
The performance
improvements from the aforementioned scenarios can be significant and
can justify the use of an index. The Query Optimizer can use the
precomputed results stored in the view’s index and avoid the cost of
aggregating or joining the underlying tables. Keep in mind that the
Query Optimizer may still use the indexes found on the member tables of
the view instead of the index on the view. The Query Optimizer uses the
following conditions in determining whether the index on the view can be
utilized:
The tables in the query FROM clause must be a superset of the tables in the indexed view’s FROM
clause. In other words, the query must contain all the tables in the
view. The query can contain additional tables not contained in the view.
The join conditions in the query must be a superset of the view’s join conditions.
The aggregate columns in the query must be derivable from a subset of the aggregate columns in the view.
All expressions in the query SELECT list must be derivable from the view SELECT list or from the tables not included in the view definition.
All columns in the query search condition predicates that belong to tables in the view definition must appear in the GROUP BY list, the SELECT list if there is no GROUP BY, or the same or equivalent predicate in the view definition.
The flip side of
performance with indexes (including those on views) is that there is a
cost in maintaining an index. This cost can adversely affect the
performance of data modifications against objects that have these
indexes. Generally speaking, indexes should not
be placed on views that have underlying data sets that are frequently
updated. Caution must be exercised when placing indexes on views that
support online transaction processing (OLTP) applications. A balance
must be struck between improving the performance of database
modification and improving the performance of database inquiry. Indexed
views improve database inquiry. Databases used for data warehousing and
decision support are usually the best candidates for indexed views.
The impact of data
modifications on indexed views is exacerbated by the fact that the
complete result set of a view is stored in the database. When the
clustered index is created on a view, you specify the clustered index
key(s) in the CREATE UNIQUE CLUSTERED INDEX
statement, but more than the columns in the key are stored in the
database. As in a clustered index on a base table, the B-tree structure
of the clustered index contains only the key columns, but the data rows
contain all the columns in the view’s result set.
The increased space
utilized by the index view is demonstrated in the following examples.
This first example creates a view and an associated index view similar
to the Adventureworks2008 Production.vProductAndDescription view used in a prior example:
result setCREATE VIEW [Production].[vProductAndDescription_2]
WITH SCHEMABINDING
AS
View (indexed or standard) to display products and
— product descriptions by language.
SELECT
p.[ProductID]
,pmx.[CultureID]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductModel] pm
ON p.[ProductModelID] = pm.[ProductModelID]
INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx
ON pm.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [Production].[ProductDescription] pd
ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
go
CREATE UNIQUE CLUSTERED INDEX [IX_vProductAndDescription_2]
ON [Production].[vProductAndDescription_2]
(
[CultureID] ASC,
[ProductID] ASC
)
The difference with this new
view is that the result set returns only the two columns in the
clustered index; there are no additional columns in the result set.
When the new view and
associated index are created, you can compare the amount of physical
storage occupied by each. The following example shows the sp_spaceused commands for each view and the associated results:
exec sp_spaceused 'Production.vProductAndDescription'
/* results
name rows reserved data index_size unused
---------------------- ---- -------- ------ ---------- ------
vProductAndDescription 1764 592 KB 560 KB 16 KB 16 KB
*/
exec sp_spaceused 'Production.vProductAndDescription_2'
/* results
name rows reserved data index_size unused
---------------------- ---- -------- ------ ---------- ------
vProductAndDescription_2 1764 64 KB 48 KB 16 KB 0 KB
*/
Take
note of the reserved space and data results for each view. The view
that was created with only two result columns takes much less space than
the view that has an index with five result columns. You need to
consider the overhead of storing these additional result columns along
with the index when creating the view and related index. Changes made to
any of the columns in the base tables that are part of the view results
must also be maintained for the index view as well.
Nonclustered indexes can be
created on a view, and they can also provide added query performance
benefits when used properly. Typically, columns that are not part of the
clustered index on a view are added to the nonclustered index. Like
nonclustered indexes on tables, the nonclustered indexes on the view
provide additional options for the Query Optimizer when it is choosing
the best query path. Common search arguments and foreign key columns
that may be joined in the view are common targets for nonclustered
indexes.
To Expand or Not to Expand
The expansion of a view to
its base tables is a key consideration when evaluating the use of
indexes on views. The SQL Server Query Optimizer can expand a view to
its base tables or decide to utilize indexes that are found on the view
itself. The selection of an index on a view is directly related to the
edition of SQL Server 2008 you are running and the expansion options
selected for a related query.
As mentioned earlier, the
Enterprise and Developer Editions are the only editions that allow the
Query Optimizer to use an indexed view to solve queries that
structurally match the view, even if they don’t refer to the view by
name. For other editions of SQL Server 2008, the view must be referenced
in the query, and the NOEXPAND hint must
be used as well for the Query Optimizer to consider the index on the
view. The following example demonstrates the use of the NOEXPAND hint:
SELECT *
FROM Production.vProductAndDescription (NOEXPAND)
WHERE cultureid = 'he'
When this example is run against the Adventureworks2008 database, the execution plan indicates that a clustered index seek will be performed, using the index on the view. If the NOEXPAND
hint is removed from the query, the execution plan will ignore the
index on the view and return the results from the base table(s). The
only exception to this is when the Enterprise or Developer Edition is
used. These editions can always consider indexed views but may or may
not choose to use them.
SQL Server also has options to force the Query Optimizer to use the expanded base tables and ignore indexed views. The (EXPAND VIEWS)
query hint ensures that SQL Server will process a query by accessing
data directly from the base tables. This option might seem
counterproductive, but it can be useful in situations in which
contention exists on an indexed view. It is also handy for testing
indexed views and determining overall performance with and without the
use of indexed views.
The following example, which utilizes the same view as the previous example, demonstrates the use of the (EXPAND VIEWS) query hint:
SELECT *
FROM Production.vProductAndDescription
WHERE cultureid = 'he'
OPTION (EXPAND VIEWS)
