How to choose a clustered index for a fact table

Question

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:

1. no identity surrogate key
2. composite primary key that includes 4 fields (all INT)
3. about 300 million rows
4. 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)

Answer 1

Please let me know what you would choose. And why.

I would normally choose a clustered columnstore index. They have the best compression, fastest scanning, automatic row group elimination on every column, and column-wise scanning and caching.

Second choice is just to cluster on the PK.

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

Note that you can then partition on any one of these columns, typically not the leading column. StoreKey might be good choice here.

Answer 2

I vote for the following PRIMARY & CLUSTERED key:

(StoreKey, CheckNumber, SaleItemKey,  -- Useful for lookups?
 DateOfSaleKey,                       -- Date last, not first
 SalesKey)                            -- Surrogate

(plus the minimum to make the otherwise-unused surrogate key happy -- perhaps simply INDEX?)

I don't vote for column-store unless you need to search on lots of different columns.