Indexex on low cardinality fields

SQL Server Administration
1

Hellow,
Can you please help me with an advice on how should I create indexes for a documents table where an important field is StatusID but StatusID has only 4 values, hence very low cardinality.
What's more is that 98% of the records are in ValidState (i.e. StatusID=1)
I DO have an index on StatusID right now but should I keep it? What should I do?

Thank You!

2

If you query for StatusIDs not in ValidState / =1, then, yes, keep the index. The index will be helpful for checking StatusID in ValidState only if that index contains ALL columns referenced in the query (i.e. it is a "covering index").

2 Likes