I'm trying to work out what field(s) to use for the clustered index of my fact tables (which all have a similar structure). I'll use FactSales as an example:
CREATE TABLE dbo.FactSales ( DateOfSaleKey int NOT NULL, -- PK, --ProfitCentreKey int NOT NULL, -- (maybe add this?) RevenueCentreKey int NOT NULL, -- PK TransactionHeaderNumber int NOT NULL, -- PK TransactionDetailNumber int NOT NULL, -- PK TableNumber int NOT NULL, TransactionOrCheckNumber int NOT NULL, TerminalKey int NOT NULL, CashierKey int NOT NULL, TimeSlotKey int NOT NULL, TransactionTypeKey int NOT NULL, TransactionDetailTypeKey int NOT NULL, MediaKey int NOT NULL, SaleItemKey int NOT NULL, SaleTypeKey int NOT NULL, SalesQuantity int NOT NULL, SalesGross smallmoney NOT NULL, SalesNet decimal (16, 8) NOT NULL, VAT decimal (16, 8) NOT NULL, ... )
The fact tables are loaded by ProfitCentre (ProfitCentreKey), which is a single restaurant, and by day (DateOfSaleKey). We don't have ProfitCentreKey in the table, because we have the more granular RevenueCentreKey, which is a section within a restaurant.
Each load looks like this:
DELETE s FROM FactSales s INNER JOIN DimOrganisation o ON s.RevenueCentreKey = o.RevenueCentreKey WHERE s.DateOfSaleKey = 20160105 AND o.ProfitCentreKey = 385 INSERT INTO FactSales (DateOfSaleKey, RevenueCentreKey, ...) SELECT DateOfSaleKey, RevenueCentreKey, ... FROM #SalesForProfitCentreAndDateOfSale
We do the delete so we don't have duplicate data.
What I'm considering
Clustered Index: DateOfSaleKey.
This might make the insert easier, but I'm not sure about the delete.
Clustered Index: DateOfSaleKey, ProfitCentreKey.
I would have to add ProfitCentreKey to the table. It might make the delete easier, but would it fragment the table as ProfitCentreKey would not load sequentially?
And the delete would be simplier:
DELETE FROM FactSales WHERE DateOfSaleKey = 20160105 AND ProfitCentreKey = 385
Any advice would really help. We are using Standard Edition (so no partitioning).