2

Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?

My playground:

  • Table with billion rows and 100 columns
  • Unique clustered index on column C1
  • Filtered index on column C2 covering 90% of rows defined as:

CREATE NONCLUSTERED INDEX IX_NC_F_Tab_C2 ON TAB(C2) WHERE C2 >= 0;

What I would like to achieve is the most optimal way to delete everything from that table which is not covered by the filtered index, like:

DELETE FROM Tab WHERE NOT C2 >= 0;

In this scenario my IX_NC_F_Tab_C2 index won't bring any improvement to the execution plan, therefore is there any way to force the estimator to somehow use it?

  • It can't use that index to find the rows, since the rows aren't in the index (which is, sort of, the point of filtered indexes). Which leaves us with the question what you mean by "somehow use it"? The only thing I can think of would be cardinality estimation, somehow, and I'd have to do a test to see if it would. But that would only potentially change the plan shape for the DELETE, not assist in finding those rows... – Tibor Karaszi Oct 16 at 7:41
  • By somehow use it I mean to get any advantage to perform the delete statement (above) vs not having that filtered index at all. I understand what is and what isn't included in the index structure and yes, all I would like to achieve is an execution plan which is faster than the one form the default delete (as above). – Bartosz X Oct 17 at 7:37
  • You didn't show us the execution plan, but unless there is some big miscalculation in selectivity and that causes changes to the plan shape, then the index won't matter. In the sense that it won't help. You already have the index, si as far as (potentially) assist with calculating selectivity, there is no more that the index can help you with. – Tibor Karaszi Oct 17 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.