Yet Another DBA (5/24/2016)
A Primary Key should not necessarily need to be clustered.Few developers, and not all the dbas I have worked with actually know what a clustered index is. So for some this article would be a Post-Doctorate dissertation.
The issue I always have is most developers place an identity column on the table and then make it the primary key and by default clustered. Whenever asked the response is:
- it to make the row unique,
- it the way we always do it,
- it is for performance reasons,
- its how we were taught at university,
- but Stackoverflow says this is the best way.
:crazy:
I see the road is very,very long.
I suppose on the bright side its not a heap.
I guess I do that in general because I have read Kimberley Tripp. She says to make the clustered index unique, narrow, immutable and preferably ever increasing. I also do it because it is easy when you are a surrogate key fan (I am a surrogate key type because this makes life easy with ORMs). Obviously for smaller secondary entity tables it doesn't matter so I give it no thought.
The only time I don't tend to do it is when a date field or similar (perhaps a period identifier), perhaps in combination with some other field, is likely to be heavily used. This seems to improve performance on larger primary tables.
Perhaps a simple philosophy, but sometimes you just have to press on and get the job done!