In our datawarehouse load, we bring data in the following manner. 1. from source to stage 2. stage to a consolidation layer 3. from consolidation layer to operational data store (ODS)

We're bringing in several tables without an identity column, only a business key consisting of 4 to 6 columns.

Example key: BranchCode, DriverName, FromDate, Description, DbaCode

-- Option 1
Create table dbo.MyTable
    <5 business key columns> (clustered index)
    15-20 other columns

We use a merge statement to load in new data into our consolidation layer - inserting when the business key doesn't exist in consolidation and updating if it exists and the data is different (we do a hashbytes calc on each column). We store the final hashbytes result in the consolidation table.

To make merge statement perform well, we've set a unique clustered index on those 5 columns. My question is: Is there a better solution to indexing this consolidation table?

I'm worried about the wide index if we add any nonclustered indexes (which we currently don't have but I think we'll be putting in at least one). I'm also worried about fragmentation.

One option was to add a surrogate key and then add a nonclustered index on the business keys and include the rest of the columns in the table (which can sometimes be 15-20 cols). This would ensure our merge statement is efficient but this would nearly double our table size. (our tables are 200 million rows, though they are partitioned on BranchCode).

-- Option 2
Create table dbo.MyTable
    SurrogateKeyID (clustered index)
    <5 business columns> (nonclustered index include the other columns)
    15-20 other columns


  • 2
    There's something about MERGE: mssqltips.com/sqlservertip/3074/… – Aaron Bertrand Nov 13 '14 at 15:49
  • Thanks @Aaron, I always keep those MERGE issues in mind. However in our case, those tables are only modifed by our ETL load and only during off-peak hours, so I don't think we're affected. – Gabe Nov 13 '14 at 16:11
  • ok, but note that there are some bugs listed there (that remain unfixed) that have nothing to do with concurrency. – Aaron Bertrand Nov 13 '14 at 17:15

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.