Surrogate primary key index VS. index on composite key(s)
Posted on 2006-10-27
First off, I want to thank all of you for the help (particularly aneeshattingal & Scott Pletcher, among others) -- you have all been incredibly helpful in my foray into index optimization.
This answer seems obvious to me...but of course in IT there's rarely *one foolproof answer*, so I'm looking for expert input:
BACKGROUND: I'm working with a table which has a composite primary key (varchar2, varchar3, int, int, int, int, int) and there's a nonclustered index on those columns. There are 3660 rows in the table.
QUESTION: While I know there are valid reasons for creating the table as such, it seems I should delete the nonclus index & put a nice, simple surrogate primary key int in there, and allow it to cluster. Then grab all procs which reference [the previous composite primary key] and reference the surrogate instead.
That is, there's too much overhead in the nonclus index... and I should remove it.
ADDITIONAL CONSIDERATION: How would you answer the same question if the rows grow to 100,000?