A partitioned table divides data into smaller, manageable chunks (partitions) based on a partition key (e.g., a date column). These partitions can be stored across multiple filegroups to distribute the data and improve performance.

In Azure SQL Database, the concept of filegroups is not supported as in on-premises SQL Server or Managed Instances. Instead, Azure SQL Database automatically manages storage and does not allow the creation of custom filegroups. However, in SQL Server or Azure SQL Managed Instances, this is achievable and useful for scenarios requiring manual control over data placement.

How to Create a Partitioned Table on Multiple Filegroups (SQL Server/Azure SQL Managed Instances)

If you’re working with SQL Server or Managed Instances, here’s how you can create a partitioned table on multiple filegroups:

Steps to Create a Partitioned Table on Multiple Filegroups

1. Create Filegroups
   Define multiple filegroups to distribute the data.

ALTER DATABASE MyDatabase 
ADD FILEGROUP FileGroup1;
ALTER DATABASE MyDatabase 
ADD FILEGROUP FileGroup2;

2. Add Files to the Filegroups
Add physical files to the filegroups.

ALTER DATABASE MyDatabase 
ADD FILE (
    NAME = 'File1',
    FILENAME = 'C:\File1.ndf'
) TO FILEGROUP FileGroup1;

ALTER DATABASE MyDatabase 
ADD FILE (
    NAME = 'File2',
    FILENAME = 'C:\File2.ndf'
) TO FILEGROUP FileGroup2;

3. Create a Partition Function
Define how the data will be divided into partitions.

CREATE PARTITION FUNCTION MyPartitionFunction (DATE)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2022-07-01');

4. Create a Partition Scheme
Map the partitions to the filegroups.

CREATE PARTITION SCHEME MyPartitionScheme
AS PARTITION MyPartitionFunction
TO (FileGroup1, FileGroup2, FileGroup1);

• FileGroup1 stores data before 2022-01-01.
• FileGroup2 stores data from 2022-01-01 to 2022-07-01.
• FileGroup1 stores data from 2022-07-01 onward.

5. Create the Partitioned Table
Use the partition scheme to create the table

CREATE TABLE PartitionedTable (
    ID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
) ON MyPartitionScheme(SaleDate);

1. The table is now partitioned, and data will be distributed based on the SaleDate column.

Limitations in Azure SQL Database

1. No Custom Filegroups
   • Azure SQL Database does not support custom filegroups or the ability to place partitions on specific filegroups.
   • Impact: You cannot control the physical storage layout of partitions. Azure SQL Database automatically manages storage across underlying resources.
2. Automatic Storage Management
   • Azure SQL Database dynamically scales storage, distributing data across its infrastructure.
   • Impact: Partitioning still works logically, but you cannot map partitions to specific physical storage.
3. Resource Constraints
   • Although Azure SQL Database simplifies storage management, performance depends on the pricing tier and resource allocation (e.g., CPU, IOPS, memory).
   • Impact: Partitioning may not yield expected performance benefits if the database resources are insufficient.
4. Partition Switching
   • Unlike SQL Server, Azure SQL Database does not support partition switching, which allows quick movement of partitions between tables.
5. 15,000 Partition Limit
   • You can have a maximum of 15,000 partitions per table.

Best Practices in Azure SQL Database

Even though Azure SQL Database doesn’t support custom filegroups, you can still use partitioning effectively:

1. Partitioning for Query Optimization:
   • Use partitioning to logically separate data (e.g., by date ranges) and improve query performance by narrowing down the search scope.
   • Example: Filter queries by the partition key to enable partition elimination.
2. Combine Partitioning with Indexing:
   • Create aligned indexes on partitioned tables to maximize query performance.
3. Choose the Right Pricing Tier:
   • Ensure the chosen tier provides sufficient resources to handle the workload, as storage and IOPS are managed by Azure SQL Database.
4. Plan for Future Growth:
   • Design partition functions with future data growth in mind to minimize table recreation.

Summary

In Azure SQL Database, creating partitioned tables on multiple filegroups is not supported, as the platform manages storage automatically. For SQL Server and Azure SQL Managed Instances, you can define filegroups, partition functions, and partition schemes to distribute data across specific storage locations. This can improve query performance and manageability for large datasets.