What is a good strategy for choosing a clustered index on a fact or transactional table? I am using SQL Server 2019.
I have picked a generic sales table (FactSales) that has these properties:
- no identity surrogate key
- composite primary key that includes 4 fields (all INT)
- about 300 million rows
- it loads continuously as each check is closed, so each load will be for a single DateOfSaleKey, StoreKey, CheckNumber (and contain many SaleItemKeys)
I can see 5 options (but there might be more), which I have scripted below -- with pros and cons.
Please let me know what you would choose. And why.
CREATE TABLE dbo.FactSales
(
DateOfSaleKey INT NOT NULL,
StoreKey INT NOT NULL,
CheckNumber INT NOT NULL, -- not unique across stores
SaleItemKey INT NOT NULL,
CashierKey INT NOT NULL,
TerminalKey INT NOT NULL,
SaleTypeKey INT NOT NULL,
TimeSlotKey INT NOT NULL,
TransactionTypeKey INT NOT NULL,
SaleTime DATETIME NOT NULL,
SalesQuantity INT NOT NULL,
SalesNet DECIMAL (16, 8) NOT NULL,
SalesGross DECIMAL (16, 8) NULL,
VAT DECIMAL (16, 8) NOT NULL,
DiscountQuantity INT NOT NULL,
Discount DECIMAL (16, 8) NOT NULL,
VoidQuantity INT NOT NULL,
Void DECIMAL (16, 8) NOT NULL,
RefundQuantity INT NOT NULL,
Refund DECIMAL (16, 8) NOT NULL,
)
ALTER TABLE dbo.FactSales ADD CONSTRAINT PK_FactSales PRIMARY KEY NONCLUSTERED (DateOfSaleKey, StoreKey, CheckNumber, SaleItemKey)
-- OPTION #1: add a surrogate key (identity) and make that the clustered index
-- unique, narrow and always increasing, but unnecessary column
ALTER TABLE dbo.FactSales ADD SalesKey INT IDENTITY NOT NULL
CREATE UNIQUE CLUSTERED INDEX CX_FactSales ON dbo.FactSales (SalesKey)
-- OPTION #2: make the primary key also the clustered index:
-- unique, but wide
ALTER TABLE dbo.FactSales ADD DROP CONSTRAINT PK_FactSales
ALTER TABLE dbo.FactSales ADD CONSTRAINT PK_FactSales PRIMARY KEY CLUSTERED (DateOfSaleKey, StoreKey, CheckNumber, SaleItemKey)
-- OPTION #3: base the clustered index on how the data is inserted
-- optimised for inserting new data, but not unique
CREATE CLUSTERED INDEX CX_FactSales ON dbo.FactSales (DateOfSaleKey, StoreKey)
-- OPTION #4: base the clustered index on how the data is selected
-- optimised for inserting new data and some reports, but not unique and getting wider
CREATE CLUSTERED INDEX CX_FactSales ON dbo.FactSales (DateOfSaleKey, StoreKey, SaleItemKey)
-- OPTION #5: base the clustered index on how the data is selected -- more selective (so it covers more reports)
-- optimised for inserting new data and more reports, but not unique and even wider
CREATE CLUSTERED INDEX CX_FactSales ON dbo.FactSales (DateOfSaleKey, StoreKey, SaleItemKey, CheckNumber)