Not able to create table Partition with Round Robin distribution

Question

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

Answer 1

Hello @Anonymous 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