How to apply partition on tables

Question

Hi ,

We are facing performance issues while inserting and selection from the table.We have few tables we are planning to implement partition on existing tables.

Please suggest how to implemet partition on exisitng tabels having data.We are using Azure Sql DB.

Answer 1

Hi
Raj0125 •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want to create Partition on existing tables in Azure SQL Database.

Below is a basic T-SQL example to create a new partition function, partition scheme, and partition an existing table. The DDL for your specific need will vary depending on the indexes on the existing tables and whether you want to partition those as well (align).

--create function with yearly boundaries
CREATE PARTITION FUNCTION PF_DatetimeExample (datetime) 
    AS RANGE RIGHT FOR VALUES('20180101','20190101','20200101','20210101');
--create scheme for the function
CREATE PARTITION SCHEME PS_DatetimeExample
    AS PARTITION PF_TEST ALL TO ([PRIMARY]);
--partition the table
CREATE CLUSTERED INDEX cdx ON dbo.YourTable(YourDatetimeColumn) 
    ON PS_DatetimeExample(YourDatetimeColumn);

With Azure SQL Database, just specify the PRIMARY for all filegroups in the partition scheme.

Reference Q&A post: https://learn.microsoft.com/en-us/answers/questions/133553/partitioning-existing-tables-in-azure-sql-database

and Document link: https://learn.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes?view=sql-server-ver16

Please let us know if this helps. If not, please share more details of the ask so that we can help.

Thank you.

Answer 2

Yes , we required to create Partition on exisitng tables.Tables not having any Date and time related columns.We have only Load_key that is generating dynamicaly during ETL load.We have Nonclustered Index is already exists based on Load_Key. Please advice how we can perform partition with out Date and time columns in table.

DON'T TO IT!

I mean, do you use the load_key in the qureies? Probably not. You are heading for a disaster, if you are going this way.

(And that is also what Dan warned you for in his posts.)