There is a plethora of information available detailing why an
IDENTITY field should be use as both the primary key and clustered index of a table for most situations; yet I'm having difficulty deciding if my particular situation is an exception.
I have a table with around 250 columns—I don't want to start a normalization debate—and around 100M rows. The table is page compressed. On peak days, around 1M rows are entered into the table sequentially with 0 contention from other connections. The combination of a
char(2) NOT NULL field and an
int NOT NULL field is guaranteed to be unique and never changing. There are only about 10 unique values for the
My argument for an
IDENTITY column being used as the clustered index is solely performance based. Since inserts will happen sequentially, I shouldn't haven't to worry about latch contention; thus the inserts should be faster since there will be no need to search the index. On the other hand, a composite key of 6 bytes is rather small; and I'll end up making it the primary key anyway (with the
char(2) field first). Furthermore, there will be a few other tables that are bulk loaded based on the new rows in this table (likely via a filtered index). If I use an
IDENTITY column, I'll likely use that column as the clustered index and primary key of these other tables (without the
IDENTITY property) and not bring over the "natural" composite key.
Does making a nonclustered index on the composite key nullify the expected speed boost of using an
IDENTITY column as the clustered index since this index will have to be searched when the inserts are done?
Based on the comments, I changed the title and am asking an amended question whose answer can be used to answer the original question above. How does maintenance of a unique clustered index compare to maintenance of a unique nonclustered index in regards to inserts? Does a non-sequential clustered index suffer similarly to a non-sequential nonclustered index?