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]))
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)
The following query also only scan one partition.
select count(*), min($partition.pfByMonth(Month)) from T where Month = '200707'
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.)