I have a table with 4 million records. The table has a PK and clustered index on a ColA (which is an identity value). ColB is a non clustered indexed varchar (500). ColC is char(1) with a non clustered index.
ColC has 17 distinct records out of 4 million.
Selectivity Ration is defined by SR = 100 * (Rk / Rt) where Rk is the total selectivity of the key and Rt is the total records. I believe I read somewhere that if the SR is less than 15% sqlserver will use a scan. In this case the SR on ColC is practicaly zero.
So, my question is; Is the index on ColC of any value?
If I have a query: Select * from table where ColC = 'A' will use a full table scan.
If I have a query: Select * from table where ColB like 'B%' and ColC = 'A'
will the index on ColC be of any value?
If I have a query: Select * from table where ColB like '%B%' and ColC = 'A' will the index on ColC be of any value?
Any advice here would be very helpful. Thank you in advance.