Sql Server doing a full table scan when first field in PK has few distinct values

Question

I have this table (TableA):

(
    [FieldA] [int] NOT NULL,
    [FieldB] [int] NOT NULL,
    [Value] [float] NULL
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
    [FieldA] ASC,
    [FieldB] ASC
)

There are few distinct FieldA values, lets say FieldA can be {1,2,3,4,5,6}.

Why does this query causes a full table scan:

SELECT COUNT(*) FROM TableA WHERE FieldB = 1

While this doesn't:

SELECT COUNT(*) FROM TableA WHERE FieldB = 1 where FieldA in (1,2,3,4,5,6)

Can't Sql Server optimize this? If I had TableB where FieldA was a PK and I joined TableB and TableA the query would run similarly to the second query.

Answer 1

Apparently, what I was looking for is a skip-scan optimization which is available on Oracle but not on SQL Server. Skip scan can utilize an index if the leading edge column predicate is missing: http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/48de15ad-f8e9-4930-9f40-ca74946bc401