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 )