Partitioning existing tables in Azure SQL Database

Question

Hi Team,

I need to do partitioning of few existing tables in my Azure SQL Database (used as PaaS).

Tried following below link,
https://learn.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes?view=azuresqldb-current

In SSMS, it is not allowing me to create the File group (Using Transact-SQL),
Keyword or statement option 'filegroup' is not supported in this version of SQL Server.

Also, through SSMS, once right click the DB -> select properties -> Under Select a page, I am unable to see an option of Filegroups,

Can you please suggest a way so that I could partition few existing tables of my Azure SQL Database.

Thanks,
Salil

Answer 1

Can you please suggest a way so that I could partition few existing tables of my Azure SQL Database.

User-defined filegroups are not a requirement for table partitioning. With Azure SQL Database, just specify the PRIMARY for all filegroups in the partition scheme.

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);

Answer 2

@salilsingh-9961

We have seen these errors in few of the issues before. Please check this document regarding - How to create partition and check if this resolves your issue.

Hope this helps or get back to us for any questions.

Thanks
Navtej S