I indexed a char datatype column but my query is still doing a table scan. So, indexes can be applied only on columns with integer values? This table has 50 million rows and the column has 4 distinct values.
The cardinality of that non-clustered index containing only that one column is very low so it is not particularly useful. As someone pointed it, it would be best if it was part of an INCLUDE clause in another index to create a covered index. However, performing a full table scan on the clustered index can be really expensive. If that index is your only criterion for data selection, it would be more resource efficient if the Query Optimizer performed a full non-clustered index scan than a full clustered index scan.
You can force the Query Optimizer to use your index by using a table hint:
"SELECT <column names> FROM <table name> WITH (INDEX(<index name>
Try this and see if it reduces your query execution time.