Database Reference
In-Depth Information
•
The view definition must be
deterministic
—that is, it is able to return only one possible result
for a given query. (A list of deterministic and nondeterministic functions is provided in SQL
Server Books Online.)
•
The indexed view must reference only base tables in the same database, not other views.
•
The indexed view may contain float columns. However, such columns cannot be included in
the clustered index key.
•
The indexed view must be schema bound to the tables referred to in the view to prevent
modifications of the table schema (frequently a major problem).
•
There are several restrictions on the syntax of the view definition. (A list of the syntax
limitations on the view definition is provided in SQL Server Books Online.)
•
The list of
SET
options that must be fixed are as follows:
•
ON
:
ARITHABORT
,
CONCAT_NULL_YIELDS_NULL
,
QUOTED_IDENTIFIER
,
ANSI_NULLS
,
ANSI_
PADDING
, and
ANSI_WARNING
•
OFF
:
NUMERIC_ROUNDABORT
■
If the query connection settings don't match these ansI standard settings, you may see errors on the insert/
update/delete of tables that are used within the indexed view.
Note
Usage Scenarios
Reporting systems benefit the most from indexed views. OLTP systems with frequent writes may not be able to take
advantage of the indexed views because of the increased maintenance cost associated with updating both the view
and the underlying base tables. The net performance improvement provided by an indexed view is the difference
between the total query execution savings offered by the view and the cost of storing and maintaining the view.
If you are using the Enterprise edition of SQL Server, an indexed view need not 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. Otherwise, you would need to directly reference it within your
T-SQL code on editions of SQL Server other than Enterprise. The query optimizer considers indexed views only for
queries with nontrivial cost. You may also find that the new columnstore index will work better for you than indexed
views, especially when you're preaggregating data. I'll cover the columnstore index in a section later in this chapter.
Let's see how indexed views work with the following example. Consider the following three queries:
SELECT p.[Name] AS ProductName,
SUM(pod.OrderQty) AS OrderOty,
SUM(pod.ReceivedQty) AS ReceivedOty,
SUM(pod.RejectedQty) AS RejectedOty
FROM Purchasing.PurchaseOrderDetail AS pod
JOIN Production.Product AS p
ON p.ProductID = pod.ProductID
GROUP BY p.[Name];
SELECT p.[Name] AS ProductName,
SUM(pod.OrderQty) AS OrderOty,
SUM(pod.ReceivedQty) AS ReceivedOty,
SUM(pod.RejectedQty) AS RejectedOty
