MS SQL Server not using index when WHERE has IN with more than 6 values

Question

So i have a very strange issue, If i run a query like this:

SELECT * 
FROM tbl_x 
WHERE tbl_x.SomeCode IN ('1','2','3','4','5','6')

The query uses the index on the table, however if i do query this:

SELECT * 
FROM tbl_x 
WHERE tbl_x.SomeCode IN ('1','2','3','4','5','6','7')

The query ignores the index and decides to do a Table Scan

Why is MS SQL Server not using the index when there are more than 6 values in the Where clause?

Answer 1

I guess that based on data distribution and cardinality query optimizer decides to use full table scan, because it is cheaper than to use index.

You could check:

SELECT COUNT(*)
FROM tbl_x;

and

SELECT COUNT(*)
FROM tbl_x
WHERE tbl_x.SomeCode IN ('1','2','3','4','5','6','7');

Probably you exceeded 20% rows of entire table.