Database Reference
In-Depth Information
Figure 4-8.
Filtered indexes: Statistics histogram
This behavior can lead to incorrect cardinality estimation and suboptimal execution plans. You should regularly
update statistics on filtered indexes when the filter columns are volatile and not included in the index key. On the positive
side, filtered indexes are usually small and index maintenance introduces less overhead than with regular indexes.
Another area where filtered indexes are very useful is supporting uniqueness on a subset of values. As a practical
example, think about a table with SSN (Social Security Number) as the optional nullable column. That scenario usually
requires you to maintain the uniqueness of the provided SSN values. You cannot use a unique nonclustered index for
such a purpose. SQL Server treats NULL as the regular value and does not allow you to store more than one row with a
non-specified SSN. Fortunately, a unique filtered index does the trick. Listing 4-14 shows such an approach.
Listing 4-14.
Filtered indexes: Updating data
create table dbo.Customers
(
CustomerId int not null,
SSN varchar(11) null,
/* Other Columns */
);
create unique index IDX_Customers_SSN
on dbo.Customers(SSN)
where SSN is not null;
Filtered Statistics
One of the assumptions with the Legacy Cardinality estimator, used in SQL Server 2005-2012, and in SQL Server 2014
with a database compatibility level of less than 120, is the independence of query predicates from each other.
To illustrate this concept, let's look at the code shown in Listing 4-15. This table stores information about Articles,
and it has a few attributes, such as
Color
and
Size
.
Listing 4-15.
Cardinality estimation with multiple predicates
create table dbo.Articles
(
ArticleId int not null,
Name nvarchar(64) not null,
Description nvarchar(max) null,
Color nvarchar(32) null,
Size smallint null
);
select ArticleId, Name from dbo.Articles where Color = 'Red' and Size = 3
