Can adding a new filtered index cause problems with other index for a table?
I have a table in my database with about 10 million records. I recently added a new non-unique field called newPieceID (VARCHAR 40) to the table and would like to create a new non-clustered filtered index that has a WHERE clause of "WHERE newPieceID is not null". I would like to make sure that doing so would not have a negative impact on the existing table indexes or accessing any existing data. Thanks in advance. Dave
The impact of a filtered index is no different than the impact caused by adding any other index. Inserts, updates and deletes will be affected because you'll have to maintain that added index. But, assuming the benefits the index gives your reads outweighs the impact on writes, it should be fine. The existence of an index won't negatively impact the optimizer selecting any other index for a given query assuming they are useful in the optimizers eyes due to their size, the selectivity or the statistics. In short, nah, should be fine.