You work for MDFT Pro, a well-known training agency that maintains course sales data in an Azure Synapse Analytics dedicated SQL pool. Mark, the Data Warehouse Administrator, has asked you to create a new fact table called FactCourseSales that will store information about course purchases, students, and trainers.
The company has a data retention policy that requires keeping sales data for exactly five years, after which old records must be deleted. Mark emphasizes that the annual data deletion process should be as fast as possible, and the data should be distributed evenly across partitions to maintain query performance.
How should you complete the Transact-SQL statement?
CREATE TABLE [dbo].[FactCourseSales]
(
[CourseKey] int NOT NULL,
[OrderDateKey] int NOT NULL,
[StudentKey] int NOT NULL,
[SalesOrderNumber] nvarchar(20) NOT NULL,
[OrderQuantity] smallint NOT NULL,
[UnitPrice] money NOT NULL
)
WITH
(
CLUSTERED COLUMNSTORE INDEX,
DISTRIBUTION = HASH(CourseKey),
PARTITION( [ _____________ ] RANGE RIGHT FOR VALUES
(20210101, 20220101, 20230101, 20240101, 20250101)
)
)
Choose the correct answer from the options below.
Explanations for each answer: