Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I wrote as script that would dynamically search through all single-column indexes containing more than 1% of null values, then drop and recreate those indexes as filtered indexes on the condition that the value was NOT NULL. This would drop and recreate hundreds of indexes throughout the database and typically free up almost 15% of the space used by the whole DB.

Now I have two questions about this:

A) What are the downsides of using filtered indexes in this fashion? I would assume that it would only improve performance, but are there any performance risks involved?

B) We received errors ('can't drop index XYZ beause it does not exist or you do not have permission') on dropping and recreating the indexes, even though when checked afterwards, everything had gone exactly as expected. How can this happen?

Thanks for any help!

Edit: In response to @Thomas Kejser

Hi and thanks, but it turns out this was a disaster. At the time we didn't understand several things such as:

  1. During a query, SQLOS makes index plans prior to determining that it cannot use NULL values for joining table columns. IE, you truly do need to have a WHERE clause filter fitting the index for each and every filtered index used in the query, or the index will not be used at all.
  2. Dropping and creating indexes and redundantly updating their statistics yet again afterwards still may not be enough to produce the updated plans, which we assumed they would. It appears in some cases only a high enough workload will force SQL Server to reassess the plans.
  3. There are some exotics to the functionality of the execution planner that are difficult to determine by common sense and logic alone. With thousands of code-behind -generated variations of different queries even, seemingly useless indexes can help in some statistics and query plans that end up being used in critical queries.

In the end, these changes were reverted. So filtered indexes are a powerful tool, but you need to truly understand exactly what data is being fetched from those columns. Where normal indexes aside from the space issues are rather easy to apply, filtered indexes represent very customized solutions. They are certainly not a replacement for a regular index, rather an extension to them in those special circumstances they're required.

share|improve this question
You may want to re-examine your indexing strategy too. If you have hundreds of single field indexes it's probably not optimal. –  JNK Jan 29 '13 at 13:46
The need for these comes from the fact that the database is partially inherited from another system. By default, we have some abstract tables, and several abstract columns that may not be used at all, which produces most of these massive amounts of indexed NULL values. As for the single-field indexes, they're created from the base requirement that each foreign key should be indexed, and many of those are in these columns that contain mostly or only NULL values. –  Kahn Jan 29 '13 at 14:35

1 Answer 1

up vote 6 down vote accepted

Very interesting approach. My upvote for the creativity.

Since you reclaimed the space, I assume the original indexes are no longer in place? The downsides of filtered indexes then are:

In practical terms, this means that you have to be extremely careful with filtered indexes as they will often result in horrible query plans. I would not go so far as to call them useless, but I view them as an addition to traditional indexes, not as a replacement (as you are trying to do).

share|improve this answer
Thanks! Added an edit to respond to this. :) –  Kahn Jan 10 '14 at 8:30
"Query parameterisation doesn't work with filtered indexes" . this can probably be fixed with option(recompile) –  MichaelD Jan 10 '14 at 8:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.