Eugene Elutin (10/4/2012)
ScottPletcher (10/4/2012)
I don't think such extremely simplistic rules always work well in tables, large or small.
Choosing the best indexes takes a lot of careful consideration of your data distribution and your query usage patterns, and there is no one-size-fits-all answer.
If a table is declared with a pk (which is advisable), by default a clustered index will be built on the primary key column(s). However, this is not always the best choice. The primary key is, by definition, unique, and nonclustered indexes are very efficient at finding a single qualifying row and can still enforce the primary key's uniqueness. So save your clustered index for something that will benefit more from it.
Yes, you are right! There is no one-size-fits-all answer to that.
However...
I guess it was some long discussions between SQL Server (and back to old days Sybase) design engineers/architects about making PK clustered by default. They decided to do so, and not only for the reason "not to create heaps". PK is not always the best choice for clustered, but quite often it is. Use of Surrogate keys for PK is quite popular in modern RDBMS's (at least in SQL Server and Oracle), so may be that is another statistical manifestation example of 80-20 rule...
The problem is, that's nothing close to the real ratio in production. ~60+% of legacy tuning for me is just changing the dopey identity clustered index to the proper clustered index, with an easy 50+% gain in performance.
A clustering key does NOT have to be unique to aid performance. That is the worst secondary myth that gets propogated by the main myth about always making the clus key an identity. It's just a thoughtless approach.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.