SQL filtered indexes: should I always put a filter on an index for optional columns?

Question

For 'large' tables, is there any reason not to put a filter on indexes for optional columns?

So for an index on column AAA (because people can search on AAA),
I can set the filter to ([AAA] IS NOT NULL).
This saves storage, so it saves money.

Some more advantages from technet:

• Improved query performance and plan quality
• Reduced index maintenance costs
• Reduced index storage costs

People say that it's good to put a filter on an index for columns that are mostly empty. But why wouldn't I put a filter on indexes for columns that are empty for like 1%? Is there any reason not to do it if it only has advantages?

Answer 1

This is usually a good idea with two gotchas:

1. The table designer has a bug (only pre Denali!). When it rebuilds a table it deletes all filters.
2. Be sure that the optimizer can tell statically that your predicate will never allow null rows to be returned. Usually, this is the case because of SQL NULL semantics (semmingly the only case where they help instead of hinder). Example: select distinct col from T will not use the index because a null value might be found. Use this: select distinct col from T where col is not null.

Filtered indexes are vastly underused. They can even be used to make a nullable column unique.

My practical recommendation: Just try it for a few month and learn for yourself if there are additional unforseen problems.

If you are into advanced SQL Server query techniques, also look ad indexed views. THey are a super set of filtered indexes (at least on Enterprise).