SQL Server offers only range partitioning our tables, but what if I want to partition my table on the basis of List of values? Is it not doable?
The answer is Yes and No both – Yes because SQL Server offers only range based partitioning and No because, there is a trick to partition the table indirectly.
Let us not talk much and directly get into action to see how list partition works.
Below code snippet creates a simple table that we will like to partition basis list of values.
CREATE TABLE list_partition ( id INT IDENTITY ( 1 , 1 ), value CHAR ( 1 ) NOT NULL ) INSERT INTO list_partition ( value ) VALUES ( 'A' ), ( 'B' ), ( 'C' ), ( 'D' ), ( 'A' ), ( 'B' ), ( 'A' ), ( 'E' ), ( 'F' ), ( 'G' ) SELECT * FROM list_partition
| ID | VALUE | -------------- | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | A | | 6 | B | | 7 | A | | 8 | E | | 9 | F | | 10 | G |
Ideally I want to assign value ‘A’ and ‘C’ to suppose partition-1, ‘B’ to partition-2 and any thing else to partition-3. Directly using partition range function over this value column will only allow value ‘B’ to go to partition-1 as it is between ‘A’ and ‘C’ which are part of partition-1. To do away with we need to create a computed-persisted column in this table. The computed column will give us an indicator, based on a logic we define, where the partitions would be assigned.
Below code snippet adds a computed-persisted column with logic to generate a new value which can be assigned to partition range function. Idea here is to generate a same indicator for all the values that we want to put in one partition.
ALTER TABLE list_partition ADD partition_flag AS ( CASE value WHEN 'A' THEN 1 WHEN 'B' THEN 2 WHEN 'C' THEN 1 ELSE 3 END ) PERSISTED SELECT * FROM list_partition
| ID | VALUE | PARTITION_FLAG | ------------------------------- | 1 | A | 1 | | 2 | B | 2 | | 3 | C | 1 | | 4 | D | 3 | | 5 | A | 1 | | 6 | B | 2 | | 7 | A | 1 | | 8 | E | 3 | | 9 | F | 3 | | 10 | G | 3 |
Let us check the number of partitions in current table (obviously it will be one)
SELECT partition_id , object_id , index_id , partition_number , rows FROM sys . partitions WHERE OBJECT_ID = OBJECT_ID ( 'list_partition' )
| PARTITION_ID | OBJECT_ID | INDEX_ID | PARTITION_NUMBER | ROWS | ----------------------------------------------------------------------- | 72057595266007040 | 1668917017 | 0 | 1 | 10 |
Below code snippet does the following:
1) Create partition range function, it will create three partitions range for the following:
Partition 1 – Partition value less than and equal 1
Partition 2 – Partition value greater than 1 and less than equal to 2
Partition 3 – Partition value greater than 2
2) Create partition scheme and attach all the partitions to primary file group
CREATE PARTITION FUNCTION pf_list_partition ( INT ) AS RANGE LEFT FOR VALUES ( 1 , 2 ); CREATE PARTITION SCHEME ps_list_partition AS PARTITION pf_list_partition ALL TO ( [PRIMARY] ) --Partition scheme 'ps_list_partition' has been created successfully. --'PRIMARY' is marked as the next used filegroup in partition scheme 'ps_list_partition'.
Having reached here, in order to partition the table we need to create a clustered index on the partition_flag using partition scheme – as shown below
CREATE CLUSTERED INDEX CI_list_partition ON list_partition ( id ASC ) ON ps_list_partition ( partition_flag )
Let us take a look at the number of partions now
SELECT partition_id , object_id , index_id , partition_number , rows FROM sys . partitions WHERE OBJECT_ID = OBJECT_ID ( 'list_partition' )
| PARTITION_ID | OBJECT_ID | INDEX_ID | PARTITION_NUMBER | ROWS | ----------------------------------------------------------------------- | 72057595266072580 | 1668917017 | 1 | 1 | 4 | | 72057595266138110 | 1668917017 | 1 | 2 | 2 | | 72057595266203650 | 1668917017 | 1 | 3 | 4 |
It is clear that there are three partitions with corresponding count of rows.
Woow…isn’t it a simple trick 🙂
Hope you enjoyed reading and ready to apply this!
Happy Learning
Lokesh Vij
I have a big fat table with large data, and I want to partition the table on the basis of State names like NY, RI, CA etc. I tried to change the given code according to my requirement but It is not helping me out, please tell me a way to partition the table on the basis of column with varchar as a datatype.
I shall be very thankful to you.
LikeLike