Why the query scan all the partitions?

Question

I have the following partitioned table

create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month)

The partition function is

CREATE PARTITION FUNCTION [pfMonth](char(6)) AS 
RANGE RIGHT FOR VALUES (N'200001', .... N'200705', N'200706', N'200707', ....)

However, the actual partition count of following query shows is 260 instead of 1?

select count(*)
from T (nolock)
where Month = '200706'

The execution plan is

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
       |--Stream Aggregate(DEFINE:([globalagg1006]=SUM([partialagg1005])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1005]=Count(*)))
                      |--Clustered Index Scan(OBJECT:([DB].[dbo].[T].[PK_T]), WHERE:([DB].[dbo].[T].[Month]=[@1]))

Update:
The query run fast and the Actual Partition Count is 1 if I run the following query.

select count(*), $partition.pfMonth(Month)
from T with (nolock)
where Month = '200706'
group by $partition.pfMonth(Month)

Update 2:
The following query also only scan one partition.

select count(*), min($partition.pfByMonth(Month))
from T 
where Month = '200707'

Update 3:
The original SQL scans only one partition now. No clue what happened. The only difference is that more data has been inserted into the table.
Answer: I used a CTE which wrapped the table. And it worked around the issue caused by type precedence. (However, the CTE didn't work around the issue at first though, the whole thing is strange and unpredictable.)

Answer 1

Your column is of type CHAR(6). Your filter is of type VARCHAR (perhaps counter intuitive, but that what the constant literal '200706' is). The rules of Data Type Precedence dictate that the comparison occurs using the type with higher precedence, ie. the VARCHAR type. This is a different type than the partitioning function, therefore you do not get partition elimination.

Try this instead:

select count(*)
from T (nolock)
where Month = CAST('200706' as CHAR(6));

Your plan should get a partitioning elimination filter.

Answer 2

This is directly related to your other question.

You don't have an index on your partition key alone, it's the second key on your PK. Because of this the engine needs to check every row. If you had an index on that field then it could check just the index.