can you please help me to get the partition number based on value in synapse analytics

Meruva-Pallampati, Madhuri 1 Reputation point
2021-12-14T13:45:07.63+00:00

Hi,

For Ms-sql server we have function to the get the partition number based on value

$PARTITION.Partition Function

Can you please help me how to achieve the same functionality in synapse analytics. Either using query to get the partition number based on the value.

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.
4,122 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Lin Wen 0 Reputation points
    2023-09-19T12:21:55.39+00:00

    I had same confusion on partition number too. from below example , why switch PARTITION 2 TO PARTITION 2?

        WITH    (   DISTRIBUTION = HASH([ProductKey])
                ,   CLUSTERED COLUMNSTORE INDEX
                ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                    (20000101,20010101
                                    )
                                )
                )
    AS
    SELECT  *
    FROM    [dbo].[FactInternetSales]
    WHERE   [OrderDateKey] >= 20000101
    AND     [OrderDateKey] <  20010101
    ;
    
    INSERT INTO dbo.FactInternetSales_NewSales
    VALUES (1,20000101,2,2,2,2,2,2);
    
    ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);
    
    0 comments No comments