Does it make sense to create index on field with less changeability ?

Question

I heard opinion that it make no sense to create indexes when field has less changeability.
Eg if it stores only A,B,C,D values then there wouldn't be benefit of having such index, and further more SQL server will not use it at all when executing query.

I wonder your opinion on that matter?

EDIT

Sample usage

Select * FROM Table WHERE Status = 'A'   -- A means Active

or combination with other fields

Select * FROM Table WHERE Group_ID = 123 AND Status = 'A'   

Answer 1

The general advice is that an index on a low-cardinality field is rarely going to be useful.

B-tree indexes are most effective for high-cardinality data (i.e. columns with many possible values, where the data in the column is unique or almost unique).

Some database engines, like Oracle and PostgreSQL, support Bitmap Indexes. Bitmap indexes have traditionally been considered to work well for data such as gender (Male or Female), which has a small number of distinct values, but with many occurrences of those values.

---

UPDATE:

Further to the sample usage, note that for your second query an index on Group_ID would probably be enough.

It is on the first query where you should consider how to restrict the results. It also depends on how frequently you plan to be running this query. In some situations, a full table scan could be the only option.

Answer 2

If the alternative is a table scan, then it's probably worth it if it's a big table, but look at the possibility for making it a covering index to get even more benefit.

Answer 3

Are you ever going to use this column in by itself? Like this:

SELECT TheCol FROM Mytable WHERE myCol = 'A'

If not, then it will be covering or composite and may benefit queries containing this column.

It's hard to say without more info

Edit: if you are doing SELECT * all bets are off anyway. However, an index on both columns for the 2nd one may help.