Azure SQL Partition existing table with an integer field as partition field

Question

I am using Azure SQL database to store data. As the dataset is really huge and it keeps growing day by day, I am looking out a way to partition the existing table based on a integer field. Below is how my table looks like

branch_code	dept	total_sale
215	Grocery	1500
1230	Cosmetics	20000
300	Cosmetics	10000
312	Jewellery	15000

There are multiple branches of a Super market and each branch holds multiple department numbers whose data will be stored and retrieved from the database on a regular basis.

To overcome performance issue with huge record count, I am trying to partition the above existing table based on Branch code.

I tried with the below query

ALTER TABLE dbo.department_data PARTITION BY (branch_code) PARTITIONS 4;

but I am getting error saying

Incorrect syntax near 'PARTITION'.

Can some one help me with a solution to partition the existing Azure SQL table based on an integer field

Answer 1

To create Azure SQL partition on existing table you need to first create Partition function and Partition schema.

You can create Partition function as below:

CREATE  PARTITION  FUNCTION BranchCodeRange1 (INT)
AS  RANGE  LEFT  FOR  VALUES (215,300,312,1230);

Partition	1	2	3	4	5
Values	branch_code <= 215	branch_code > 215 AND branch_code <= 300	branch_code > 300 AND branch_code <=312	branch_code > 312 AND branch_code <=1230	branch_code > 1230

After this you need to create the Partition schema:

CREATE  PARTITION  SCHEME BranchCodeScheme1
AS  PARTITION BranchCodeRange1
ALL  TO ([PRIMARY]);

Then create Clustered Index on your table with Partition Function we created above:

CREATE  CLUSTERED  INDEX IX_department_data_partitioncol ON dbo.department_data (branch_code)
WITH (STATISTICS_NORECOMPUTE  =  OFF, IGNORE_DUP_KEY  =  OFF,
ALLOW_ROW_LOCKS  =  ON, ALLOW_PAGE_LOCKS  =  ON)
ON BranchCodeScheme1(branch_code)

OUTPUT: