Table partition in Azure MI

Sayali Khot (skhot) 20 Reputation points
2024-06-13T14:16:41.6233333+00:00

Hi,
Can we create Table partitions in Azure SQL server Managed Instance connection?
If yes, can we create partitions using T-SQL?

Regards,

Sayali.

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. Oury Ba-MSFT 17,946 Reputation points Microsoft Employee
    2024-06-13T16:24:01+00:00

    @Sayali Khot (skhot) Thank you for reaching out.

    Yes, you can create a partitioned table or index in Azure SQL Managed Instance by using SQL Server Management Studio or Transact-SQL. In Azure SQL Managed Instance we could create different filegroups and files for each partition having a better performance having multiple files per database.

    You can create table partitioning in one filegroup or multiple filegroup using T-SQL.

    CREATE DATABASE PartitionTest;

    GO

    USE PartitionTest;

    GO

    ALTER DATABASE PartitionTest

    ADD FILEGROUP test1fg;

    GO

    ALTER DATABASE PartitionTest

    ADD FILEGROUP test2fg;

    GO

    ALTER DATABASE PartitionTest

    ADD FILEGROUP test3fg;

    GO

    ALTER DATABASE PartitionTest

    ADD FILEGROUP test4fg;

    ALTER DATABASE PartitionTest

    ADD FILE

    (

    NAME = partitiontest1,  
    
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest1.ndf',  
    
    SIZE = 5MB,  
    
    FILEGROWTH = 5MB  
    

    )

    TO FILEGROUP test1fg;

    ALTER DATABASE PartitionTest

    ADD FILE

    (

    NAME = partitiontest2,  
    
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest2.ndf',  
    
    SIZE = 5MB,  
    
    FILEGROWTH = 5MB  
    

    )

    TO FILEGROUP test2fg;

    GO

    ALTER DATABASE PartitionTest

    ADD FILE

    (

    NAME = partitiontest3,  
    
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest3.ndf',  
    
    SIZE = 5MB,  
    
    FILEGROWTH = 5MB  
    

    )

    TO FILEGROUP test3fg;

    GO

    ALTER DATABASE PartitionTest

    ADD FILE

    (

    NAME = partitiontest4,  
    
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest4.ndf',  
    
    SIZE = 5MB,  
    
    FILEGROWTH = 5MB  
    

    )

    TO FILEGROUP test4fg;

    GO

    CREATE PARTITION FUNCTION myRangePF1 (datetime2(0))

    AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;  
    

    GO

    CREATE PARTITION SCHEME myRangePS1

    AS PARTITION myRangePF1  
    
    TO (test1fg, test2fg, test3fg, test4fg) ;  
    

    GO

    CREATE TABLE PartitionTable (col1 datetime2(0) PRIMARY KEY, col2 char(10))

    ON myRangePS1 (col1) ;  
    

    GO

    Create partitioned tables and indexes

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

    https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-60-using-partition-option-in-azure-sql-managed/ba-p/369165

    Benefits of partitioning

    Hope that helps.

    Regards,

    Oury

    0 comments No comments

0 additional answers

Sort by: Most helpful