azure-sql-databaseazure-sql-managed-instance

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

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

Solution

  • 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:

    enter image description here