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:

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

  1. Clustered Index: DateOfSaleKey.

    This might make the insert easier, but I'm not sure about the delete.

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

  • You should probably consider upserting instead. E.g with merge in TSQL or a lookup transformation in SSIS. – Martin Smith Apr 21 '16 at 16:02
  • But wouldn't the choice of clustered index affect a MERGE as well? – Craig HB Apr 21 '16 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.