Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?
- 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?