How to Drop a Partition in Azure Synapse Dedicated SQL Pool?

Narayana Nayudu 60 Reputation points
2023-02-10T09:08:04.5633333+00:00

I have a table in Azure Synapse Dedicated SQL Pool. In that Table, I have added 3 partitions. Now I want to delete 1 Partition form that table. How to do that?

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,378 questions
{count} votes

Accepted answer
  1. Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
    2023-02-15T23:00:59.9766667+00:00

    Hello @Narayana Nayudu,

    The partition switching can be used when the table is not empty.

    If the table is empty, then you can merge the empty partitioning using the Mege command.

    Syntax:

    ALTER TABLE table_name MERGE RANGE ("range");

    To demonstrate this:

    CREATE TABLE [dbo].[FactInternetSales]
    (
            [ProductKey]            int          NOT NULL
        ,   [OrderDateKey]          int          NOT NULL
        ,   [CustomerKey]           int          NOT NULL
        ,   [PromotionKey]          int          NOT NULL
        ,   [SalesOrderNumber]      nvarchar(20) NOT NULL
        ,   [OrderQuantity]         smallint     NOT NULL
        ,   [UnitPrice]             money        NOT NULL
        ,   [SalesAmount]           money        NOT NULL
    )
    WITH
    (   CLUSTERED COLUMNSTORE INDEX
    ,   DISTRIBUTION = HASH([ProductKey])
    ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                        (20000101
                        )
                    )
    );
    
    The following query finds the row count by using the 
    
    SELECT  QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
    ,       i.[name] as Index_name
    ,       p.partition_number as Partition_nmbr
    ,       p.[rows] as Row_count
    ,       p.[data_compression_desc] as Data_Compression_desc
    FROM    sys.partitions p
    JOIN    sys.tables     t    ON    p.[object_id]   = t.[object_id]
    JOIN    sys.schemas    s    ON    t.[schema_id]   = s.[schema_id]
    JOIN    sys.indexes    i    ON    p.[object_id]   = i.[object_Id]
                                AND   p.[index_Id]    = i.[index_Id]
    WHERE t.[name] = 'FactInternetSales';
    
    Insert records 
    
    INSERT INTO dbo.FactInternetSales
    VALUES (1,19990101,1,1,1,1,1,1);
    
    INSERT INTO dbo.FactInternetSales
    VALUES (1,20000101,1,1,1,1,1,1);
    
    If the table is not empty you can't merge the range
    
    ALTER TABLE FactInternetSales MERGE RANGE (20000101);
    
    Error message: 
    Msg 35344, Level 15, State 1, Line 39
    MERGE clause of ALTER PARTITION statement failed because two nonempty partitions containing a columnstore index cannot be merged. Consider an ALTER TABLE SWITCH operation from one of the nonempty partitions on table 'Table_90a0ae648e364d97ba15fb353d270069_2' to a temporary staging table and then re-attempt the ALTER PARTITION MERGE operation. Once completed, use ALTER TABLE SWITCH to move the staging table partition back to the original source table.
    
    Then Truncate the table: 
    
    Truncate table FactInternetSales
    
    Then you can merge the range 
    
    ALTER TABLE FactInternetSales MERGE RANGE (20000101);
    
    You can check the new count by the below command. 
    
    SELECT  QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
    ,       i.[name] as Index_name
    ,       p.partition_number as Partition_nmbr
    ,       p.[rows] as Row_count
    ,       p.[data_compression_desc] as Data_Compression_desc
    FROM    sys.partitions p
    JOIN    sys.tables     t    ON    p.[object_id]   = t.[object_id]
    JOIN    sys.schemas    s    ON    t.[schema_id]   = s.[schema_id]
    JOIN    sys.indexes    i    ON    p.[object_id]   = i.[object_Id]
                                AND   p.[index_Id]    = i.[index_Id]
    WHERE t.[name] = 'FactInternetSales';
    
    

    I hope this helps. Please let me know if you have any further questions.

    If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.