Evaluate and comment on the following general statement:

In a well designed database, the clustered index should never be on the primary key.

The point being that the normal use case isn't "I know the primary key and need to find the rest of the columns", but instead "I know data in 3 columns, show me all the records that match so I can find related data using the PK elsewhere in the database".

I think that this is the single biggest performance blunder done by programmers and n00b dba's. It's also incredibly inconvenient to fix, requiring a lot of locking and basically 2 complete table reorgs (someday someone will explain why dropping a clustered index is such a hard operation, but please not in this post).

The only case I can think of where it's always false is a table where you have a surrogate key (usually an identity column of some kind) providing the PK, where the surrogate key is provided to customers (i.e. an invoice number or confirmation number).

I'll question the validity of your business requiring customers to remember invoice numbers and the impact on your perceived customer service later.

rs.