question

AnshumanBanerjee-4976 avatar image
0 Votes"
AnshumanBanerjee-4976 asked MartinJaffer-MSFT commented

Not able to create table Partition with Round Robin distribution

Created a table with partitions and with round robin distribution as below:

DROP TABLE [dbo].[FactInternetSales]
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 = ROUND_ROBIN
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101,20020101
,20030101,20040101,20050101
)
)
)
;

Now, when I check partition metadata with the below query, no rows are being returned:

SELECT p.[partition_number], cast(r.[value] as INT) AS [high_value], i.[type_desc] AS [index_type],
p.[data_compression_desc], tdp.distribution_policy_desc , c.name AS hash_key
FROM sys.tables t INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
LEFT JOIN sys.pdw_table_distribution_properties tdp on t.object_id = tdp.object_id
LEFT OUTER JOIN sys.columns c ON t.[object_id] = c.[object_id]
left JOIN sys.partition_schemes ps ON ds.[data_space_id] = ps.[data_space_id]
left JOIN sys.partition_functions pf ON ps.[function_id] = pf.[function_id]
LEFT JOIN sys.partition_range_values AS r ON pf.[function_id] = r.[function_id] AND r.[boundary_id] = p.[partition_number]
left JOIN sys.pdw_column_distribution_properties cdp ON t.[object_id] = cdp.[object_id] and cdp.[column_id] = c.[column_id]
WHERE cdp.distribution_ordinal = 1 and SCHEMA_NAME([schema_id]) = 'dbo' and t.name = 'FactInternetSales';

Above query returns correct results if the table is created with a distribution hash key as below:

DROP TABLE [dbo].[FactInternetSales_test]
CREATE TABLE [dbo].[FactInternetSales_test]
(
[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,20010101,20020101
,20030101,20040101,20050101
)
)
)
;

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered MartinJaffer-MSFT commented

Hello @AnshumanBanerjee-4976 and welcome to Microsoft Q&A.

I was able to reproduce your findings. I did change the two tables to have the same name, to run the test more easily, and only have one possible table extant at a time.

After confirming no rows were returned, I wondered "What if the cdp.distrubution_ordinal was not 1?" So I re-created the first table, and removed the clause cdp.distribution_ordinal = 1 from your query. The query ran and returned rows. Results attached.

There was one more thing that bothered me. Your code does not insert data. Without data, how are you sure everything is behaving as expected?143909-distribution-test.txt



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks a lot @MartinJaffer-MSFT. As was trying to list hash key while querying the partition info, had used the condition distribution_ordinal = 1. I missed it completely while querying tables with round_robin where there is no hash key.
This is a dummy query as in my actual scenario I do have data.

Thanks again!

0 Votes 0 ·

Thanks for letting me know!

0 Votes 0 ·