Question
You plan to create a table in an Azure Synapse Analytics dedicated SQL pool.
Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted.
You need to ensure that the data is distributed evenly across partitions. The solution must minimize the amount of time required to delete old data.
How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all.
```sql
CREATE TABLE [dbo].[FactSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
)
WITH
(
CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = [Target 1] ([ProductKey])
, PARTITION ( [ [Target 2] ] RANGE RIGHT FOR VALUES
(20170101, 20180101, 20190101, 20200101, 20210101)
)
)
```
Explanation
Target 1 is HASH, because the statement names a distribution column, [ProductKey], and only hash distribution takes a column; ROUND_ROBIN and REPLICATE do not. Target 2 is OrderDateKey, because the yearly boundary values are dates in yyyymmdd form, so each partition holds one year of orders. Dropping or switching out the oldest year's partition then takes seconds, whereas deleting the same rows one by one could take hours. CustomerKey and SalesOrderNumber do not match date boundaries and cannot separate data by age.
