The Other side of Filtered Index

 
I see many like the idea of filtered index. This cooooooooooool new feature allows an index to be created on a subset of the data. I have seen many presentations praising this feature.

Most of you who attended the session on “The other side of indexes”  at the last Tech Ed would have enjoyed the session well. Pinal Dave was giving a very good explanation on when to use indexes and when not to use them. He mentioned on Filtered index and explained how it will benefit certain type of queries.


If you have a large table (by rows) your index will be larger.  It adds complexity over the maintenance plans you need to build.  Additionally, statistics around larger indexes can get skewed easily. This may lead to sub-optimal execution plans chosen by SQL Server Query engine.

On of the key features on filtered index is the WHERE clause. The condition may not reference the key columns of the index For example, CREATE INDEX IndexName ON TableName (Column1) WHERE Column2 >100 is a valid statement. This allows a high level of logical partitioning of data. You can create an index for column1 where Column2 is mentioned in the definition but not in the storage.

I am inspired by the feature. Wow filtered index can make wonders.  It will ease some pain points from DBAs. Suddenly a question came into my mind:  Can I create multiple filtered indexes on the entire table by