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

Meruva-Pallampati, Madhuri 1 Reputation point


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

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

        WITH    (   DISTRIBUTION = HASH([ProductKey])
                ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
    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