Any benefit to having multiple filtered indexes?

by Ken   Last Updated August 13, 2019 22:06 PM - source

I have a SQL Server database and stumbled across a table with a uniqueidentifier primary key named ActivityId and columns StateCode (int) and ActivityTypeCode (int) along with many others. The tables has 13M rows and there are 94 unique values for ActivityTypeCode with row counts ranging from 1 to 4M. OK, here’s the weird part: I have 97 different filtered indexes on (StateCode, ActivityId) where the only difference is the where clause on (ActivityTypeCode=(_some_value_)).

To my eyes, all 97 indexes could be dropped and replaced with one index on just StateCode (since ActivityID is the primary key and is already included as a hidden column). My question is: Is there any reason why I would want to keep multiple filtered indexes? Could they reduce deadlocks somehow? I’ve done some testing and I don’t see any reason to have so many indexes. Thoughts?


Related Questions

SQL Server 2008 - Question about index behaviour

Updated June 16, 2015 12:02 PM

Why is my filtered index being ignored?

Updated May 05, 2019 21:06 PM

Filtered index not chosen, and rejected with hint

Updated December 04, 2018 19:06 PM