ScottPletcher (3/4/2013)
Again, rigid overly-simplistic rules such as those will give you a poorly performing database with excessive nonclustered indexes to cover all the queries the clus index can't because its keys were so poorly chosen.
So 'keep the keys for a unique index to the columns that need to be enforced unique' is a 'rigid overly-simplistic rule'?
Maybe you want to read what I said again, that for a unique index (one that's designed for enforce uniqueness) you should keep the keys to the columns that need to be unique, otherwise you have a constraint that isn't constraining anything in a useful way.
Oh, and since I've never once seen a system where all queries against a table query on a single column or set of columns, no matter how you select the clustered index you are going to need nonclustered indexes (not that I said anything about how to select the clustered index keys in this thread). Unless of course you have that idealistic and perfect system where each and every table is only ever filtered or joined on a single column or set of columns. Never seen one of those in reality though.
MS has engineers with vastly more expertise at scale than any of us.
True, the CAT team (Mark, Lubor, Cindy and the rest are awesome). Pity those skilled engineers didn't work on the replication tables. Or on Sharepoint. Or on several other examples of terrible database design that came out of MS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability