Sql-server – Why the query scan all the partitions

partitioningsql serversql-server-2008

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.)

Best Answer

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.

Related Question