Sql Server : Partition function ‘%1’ uses %2 columns which does not match with the number of partition columns used to partition the table or index
- Posted by Sqltimes
- On April 30, 2014
- 0 Comments
Quick one today:
A few days go, as part of implementing table partitioning, I ran into this interesting error.
|
1
2
|
Msg 2726, Level 16, State 1, Line 1Partition function 'pf_SampleTable' uses 1 columns which does not match |
At first glance, the error message did not make any sense, but after reading it over a couple of times it started to make sense (this is another reason why grammar is important, even in error messages)
This error is because of not mentioning the same number of columns in the”ON” clause of the “CREATE TABLE” section as the number of columns mentioned in the partitioning function definition.
Let us take an example to fully understand this simple, but nebulous error message.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
---- Partition function - INT--CREATE PARTITION FUNCTION pf_SampleTable_PartitionKey (INT)AS RANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000)GO---- Partition Scheme--CREATE PARTITION SCHEME ps_SampleTable_PartitionKey AS PARTITION pf_SampleTable_PartitionKeyTO (ST_FG1, ST_FG2, ST_FG3, ST_FG4, ST_FG5, ST_FG6, ST_FG7, ST_FG8, ST_FG9, ST_FG10, ST_FG_n)GO |
When I created the table ‘SampleTable’, we specify partition function name in the filegroup area to partition table according to the configuration specified in the partition function & scheme.
|
1
2
3
4
5
6
7
8
9
|
---- Partition table uisng partition function--CREATE TABLE dbo.SampleTable( SampleTable_ID INT NOT NULL IDENTITY(1,1) , LocalID BIGINT NOT NULL , ServerSerial INT NOT NULL) ON [pf_SampleTable_PartitionKey]GO |
The error is caused because in the ON clause, I did not mention the column name to use for partitioning the table. Once I changed the code, it worked well.
|
1
2
3
4
5
6
7
8
9
|
---- Partition table uisng partition function--CREATE TABLE dbo.SampleTable( SampleTable_ID INT NOT NULL IDENTITY(1,1) , LocalID BIGINT NOT NULL , ServerSerial INT NOT NULL) ON pf_SampleTable_PartitionKey(SampleTable_ID)GO |
Hope this helps,
_Sqltimes
0 Comments