SQL Server : ETL Stage Index

Question

We have a very large staging table (> 80 GB). From our source system we load invoice data in the staging table. From the staging we transform the data and load into DWH/Fact. Every day we delete the current month, then reload from source into stage. The stage contains complete history over time.

In some DW loads we only need the current month. Sometimes year and previous year.

What is a better index strategy:

1. Clustered index on a date column (Fiscal Period)
2. Primary key with IDENTITY as surrogate key
3. Clustered index for natural key (some kind of line item e.g. invoice number)

All queries contain the date column (Fiscal Period) and sometimes additional columns like Invoice type as non-clustered index. In the ETL we can disable the non-clustered index but not the clustered index.

Which of the three types has the best performance for:

• Insert into Stage table
• Query the Stage table

Answer 1

If it possible for you to use partitioning, I would highly recommend leveraging it in this situation as you know that you're working with periodic data loads which fall into months or years. With partitioning, you could reserve the creation of your indexes on other columns that you may need on things such as InvoiceNo, etc. Let partitioning take care of isolating which month or period you are working with.

If you are fairly sure about the fact that date is integral portion of your join or searches, then you I would associate a "smartkey" for your loading strategy and place a clustered index on that instead.

If you are truly determined to use the date as an index seek, then I would place an non-clustered index on this field as you'll likely have to scan rows in your use-case anyway. Keep in mind creating a clustered index on date and then incurring updates or deletes will fragment your table.

Answer 2

For date key you might prefer a number. So for April 7th 2016 201604 (Or 20160407 in case of extended requirements) might be best for your date. I don't think that in a DW you need an IDENTITY column for a primary key; After all, you're more aggregating then asking to see a specific value. What I do is make sure the index avoids duplication of values: for example, having a clustered index which consists of several columns. This way I can identify a specific row and make sure there are no duplication, and yet avoids the "IDENTITY" which could reach a very large number (and for no usability reason).