Earlier, this week, I was playing with Table Partitioning on my test environment and ran into this issue. I never ran into this issue before, but it was interesting to run into it this time. Below is the error message:

Msg 7705, Level 16, State 2, Line 1
Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.

[sourcecode language=”sql”]
CREATE PARTITION FUNCTION pf_by_integer_month (SMALLINT)
AS
RANGE LEFT FOR VALUES (201301, 201302, 201303, 201304, 201305, 201306, 201307, 201308, 201309, 201310, 201311, 201312)
GO
[/sourcecode]

In the past, I’ve created table partitioning on columns with more common data types, like INT or DATETIME. But this time I wanted to try something different; SMALLINT. It made perfect sense in my mind to use a smaller datatype than INT, hoping to save a couple of bytes every record. But the partitioning key values I have did not fit into SMALLINT range. I did not realize this until now.

But now, I do. My partitioning keys are like this:

201301 — for January
201302 — for February
201303 — for March
…
…
201312 — for December

[sourcecode language=”sql”]
CREATE PARTITION FUNCTION pf_by_integer_month (INT)
AS
RANGE LEFT FOR VALUES (201301, 201302, 201303, 201304, 201305, 201306, 201307, 201308, 201309, 201310, 201311, 201312)
GO
[/sourcecode]

Obviously, the partition function ranges are 6 digit integers, but much higher than the range of SMALLINT. I modified data type of partitioning function to INT and it worked fine.
Simple, but interesting.

Hope this helps,
_Sqltimes

PS: When the datatype of partition function and datatype of the partitioning column in the table do not match, we get this error:

Msg 7726, Level 16, State 1, Line 1
Partition column ‘YearMon’ has data type smallint which is different from the partition function ‘pf_by_integer_month’ parameter data type int.