Any benefit to having multiple filtered indexes?

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

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?

Ken



Related Questions


Updated June 16, 2015 12:02 PM

Updated November 15, 2018 15:06 PM

Updated May 06, 2019 12:06 PM

Updated May 05, 2019 21:06 PM

Updated December 04, 2018 19:06 PM