1036
Part VII: Performance Tuning and Optimization
Another situation that might benefi t from fi ltered indexes is building a unique index that
includes multiple rows with null values. A normal unique index enables only a single row to
include a null value in the key columns. However, building a unique index that excludes null
in the WHERE clause creates a unique index that permits an unlimited number of null values.In a sense, SQL Server has had fi ltered indexes since SQL Server 2000 with indexed views.
There’s no reason why an indexed view couldn’t have included a WHERE clause and included
data from a fi ltered set of rows. But fi ltered indexes are certainly easier to create than
indexed views, and they function as normal nonclustered indexes — which is an excellent
segue into the next topic, indexed views.Indexed Views
When a denormalized and pre-aggregated data solution needs to be in real time, an excel-
lent alternative to querying the base tables includes using indexed views. Indexed views
are “materialized” in that when the base table is updated the index for the view is also
updated. This stores pre-aggregated or deformalized data without using special program-
ming methods to do so.Instead of building tables to duplicate data and denormalize a join, a view can be created
that can join the two original tables and include the two source primary keys and all the
columns required to select the data. Building a clustered index on the view physically
materializes every column in the select column list of the view.Although indexed views build on normal views, they should not be confused. A normal view is a saved
SELECT statement—no data is physically stored, as explained on Chapter 11, “Projecting Data
Through Views.” Indexed views actually store the data on disk. An indexed view is Microsoft’s terminol-
ogy for a materialized view.Numerous restrictions exist on indexed views, including the following:■ (^) The ANSI null and quoted identifi er must be enabled when the base tables are cre-
ated, when the view is created, and when any connection attempts to modify any
data in the base tables.
■ (^) The index must be a unique clustered index; therefore, the view must produce a
unique set of rows without using distinct.
■ (^) The tables in the view must be tables (not nested views) in the local database and
must be referenced by means of the two-part name (schema.table).
■ (^) The view must be created with the option with schema binding.
As an example of an indexed view used to denormalize a large query, the following view
selects data from the Product, WorkOrder, and ScrapReason tables to produce a view of
scrapped products:
USE AdventureWorks2012
SET ANSI_Nulls ON;
c45.indd 1036c45.indd 1036 7/31/2012 10:16:44 AM7/31/2012 10:16:44 AM
http://www.it-ebooks.info