Special Indexing and Storage Features - Pro SQL Server Internals - page 87

■ the SQl Server Query optimizer has a design limitation, which can lead to suboptimal execution plans
when columns from the filter are not present in leaf-level index rows. always add all columns from the filter to the index,
either as key or included columns.

Important

Filtered indexes have a few limitations. Only simple filters are supported. You cannot use a logical OR operator,
and you cannot reference functions and calculated columns.
Another important limitation of filtered indexes relates to plan caching. SQL Server would not use a filtered index
when the execution plan needs to be cached and the index cannot be used with any combination of parameter values.
For example, the IDX_Data_Unprocessed_Filtered index would not be used with the parameterized query shown in
Listing 4-10 even if @Processed=0 at the time of compilation.

Listing 4-10. Filtered indexes: Parametrized query

select top 1000 RecId, /* Other Columns */
from dbo.Data
where Processed = @Processed
order by RecId;

SQL Server cannot cache the plan, which is using a filtered index, because this plan would be incorrect for the
calls with @Processed=1 . The solution here is to use a statement-level recompile with option (recompile) , use
dynamic SQL, or add an IF statement, as shown in Listing 4-11.

Listing 4-11. Filtered indexes: Rewriting a parameterized query with an IF statement

if @Processed = 0
select top 1000 RecId, /* Other Columns */
from dbo.Data
where Processed = 0
order by RecId;
else
select top 1000 RecId, /* Other Columns */
from dbo.Data
where Processed = 1
order by RecId;

■ Note We will discuss plan caching in greater depth in Chapter 26, “plan Caching.”

Another very important aspect that you need to remember when dealing with filtered indexes is how SQL Server
updates statistics on them. Unfortunately, SQL Server does not count the modifications of columns from the filter
towards the statistics update threshold.
As an example, let's populate the dbo.Data tables with some data and update statistics after that. The code for
doing this is shown in Listing 4-12.