I recently was approached by some fellow DBA friends of mine inquiring about filtered indexes in SQL 2012 (never really got into them too much in 2008; just on a minimal basis). In researching I found that they could be very useful for what I encounter on a daily basis.

What exactly is a filtered index? The best way that I can describe it is an optimized non clustered index that is used to cover queries pulled from a very unique and defined subset of data. In researching, I have found that it uses a filter predicate that indexes a portion of rows in a table; not only can it improve query performance, which we all want, but is also can reduce the index maintenance and storage cost.

Filtered vs. Full-Table Indexes

Accuracy is key here. Because the filtered index only covers the rows in the filtered index the statistics will be more accurate; it also is smaller than a full table non clustered index

Reducing Index Storage Costs

Everyone always talks about disk storage and how important it is. A filtered index can greatly reduce the storage, especially when a full table index is not necessary.

Reduction in Maintenance Costs

A filtered index is only changed when the data within that filter changes; and if you think about because of the smaller size of the index it reduces the cost of updating the statistics.

What kind of Permissions are needed

Filtered indexes require Alter permissions on the table or view. The user must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Limitations / Restrictions

With everything else filtered indexes do have their limitations, restrictions, and yes even considerations in how you design them.

Some of the limitations are noted below:

1. A filtered index cannot be created on a view. However there is a gotcha to this which when research is done will show you how the query optimizer can benefit from a filtered index on a table that utilizes a view
2. Filtered indexes are defined on one table

Other Notes

With any new trick so to speak you always need to try it out in a sandbox or a local environment. I behoove you to conduct your own research prior to implementing. This post just starts to scratch the surface with filtered indexes. I’ve enjoyed the research and testing I’ve done thus far with them on 2012; I think it is definitely worth checking out if you are looking for some new ways to incorporate indexing