[SOLVED] Drop a Partition in Azure Synapse Dedicated SQL Pool

If you have multiple partitions in the Azure Synapse Dedicated SQL Pool and want to delete or drop one of the tables, here is how you can do it without any trouble.

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

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

Syntax:

ALTER TABLE table_name MERGE RANGE (“range”);

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