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

Meruva-Pallampati, Madhuri 1

Hi,

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

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.

Saurabh Sharma 23,821 Microsoft Employee

Hi @Meruva-Pallampati, Madhuri ,

Thanks for using Microsoft Q&A!!
Can you please check sys.partition_functions if this helps to get you what you are looking for? Please see the sample query below -

SELECT sch.name AS [schema_name],  
tbl.[name] AS [table_name],  
ds.type_desc,  
prt.[partition_number],  
rng.[value] AS [current_partition_range_boundary_value],  
prt.[rows] AS [partition_rows]  
FROM sys.schemas sch  
INNER JOIN sys.tables tbl ON sch.schema_id = tbl.schema_id  
INNER JOIN sys.partitions prt ON prt.[object_id] = tbl.[object_id]  
INNER JOIN sys.indexes idx ON prt.[object_id] = idx.[object_id] AND prt.[index_id] = idx.[index_id]  
INNER JOIN sys.data_spaces ds ON idx.[data_space_id] = ds.[data_space_id]  
INNER JOIN sys.partition_schemes ps ON ds.[data_space_id] = ps.[data_space_id]  
INNER JOIN sys.partition_functions pf ON ps.[function_id] = pf.[function_id]  
LEFT JOIN sys.partition_range_values rng ON pf.[function_id] = rng.[function_id] AND rng.[boundary_id] = prt.[partition_number]  
WHERE sch.name = '<Schema Name>' AND tbl.name = '<Table Name>'

Please let me know if you have any questions.

Thanks
Saurabh

Saurabh Sharma 23,821 Reputation points Microsoft Employee

2021-12-27T20:49:39.427+00:00

Hi @Meruva-Pallampati, Madhuri ,
We haven't heard back from you. Just wanted to check if you are you still facing the issue? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Saurabh
Saurabh Sharma 23,821 Reputation points Microsoft Employee

2022-01-03T20:10:22.69+00:00

Hi @Meruva-Pallampati, Madhuri ,
I have not heard back from you. Did my answer solve your issue?

Thanks
Saurabh

1 answer

Lin Wen 0

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

Share via

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

1 answer

Your answer