LIST Partitioning in SQL Server

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

2 thoughts on “LIST Partitioning in SQL Server

  1. Muhammamd Sohaiab Amene

    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.

    Like

  2. Kannan Chandrasekaran

    This works to some extent but not for all the scenarios. partition with state and the state with year wise data. In that case List and range will come into picture and will become composite partition. SQL server will not support composite partition only evolved database like oracle can support such scenarios. Better to use oracle instead of using SQL server.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s