Database Reference
In-Depth Information
Filtered indexes require a specific set of ANSI settings when they are accessed or created.
•
ON
:
ANSI_NULLS
,
ANSI_PADDING
,
ANSI_WARNINGS
,
ARITHABORT
,
CONCAT_NULL_YIELDS_NULL
,
QUOTED_IDENTIFIER
OFF
:
NUMERIC_R0UNDAB0RT
When completed, drop the testing index.
•
DROP INDEX Sales.SalesOrderHeader.IX_Test;
Indexed Views
A database view in SQL Server is similar to a virtual table but is just the output of a
SELECT
statement. You create a
view using the
CREATE VIEW
statement, and you can write queries against it exactly as if it were a table. A view doesn't
store any data—only the
SELECT
statement associated with it. Every time a view is queried, the
SELECT
statement that
defines the view is sent to the optimizer.
A database view can be materialized on the disk by creating a unique clustered index on the view. Such a view
is referred to as an
indexed view
or a
materialized 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 costly operations during query execution. After the view is materialized, multiple nonclustered indexes can
be created on the indexed view. Effectively, this turns a view (again, just a query) into a real table with defined storage.
Benefit
You can use an indexed view to increase the performance of a query in the following ways:
•
Aggregations can be precomputed and stored in the indexed view to minimize expensive
computations during query execution.
•
Tables can be prejoined, and the resulting data set can be materialized.
•
Combinations of joins or aggregations can be materialized.
Overhead
Indexed views can produce major overhead on an OLTP database. Some of the overheads of indexed views are as follows:
•
Any change in the base tables has to be reflected in the indexed view by executing the view's
SELECT
statement.
•
Any changes to a base table on which an indexed view is defined may initiate one or more
changes in the nonclustered indexes of the indexed view. The clustered index will also have to
be changed if the clustering key is updated.
•
The indexed view adds to the ongoing maintenance overhead of the database.
•
Additional storage is required in the database.
The restrictions on creating an indexed view include the following:
•
The first index on the view must be a unique clustered index.
•
Nonclustered indexes on an indexed view can be created only after the unique clustered index
is created.
