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