GilaMonster (3/4/2013)
ScottPletcher (3/4/2013)
GilaMonster (3/4/2013)
dembones79 (3/4/2013)
Should the region and district columns be included in the unique clustered index of the view?Bear in mind that a clustered index implicitly includes all the columns in the table/view. For the key I'd specify the minimum of columns, just what is the unique portion. The rest of the columns are in the index too.
If it were a nonclustered index I'd still put just the unqiue columns in the key, the others would be include columns.
If you (almost) always select by multiple columns, and depending on the selectivity of the columns, you might get significant gains from including another column in the clus index. Don't follow too simplistic rules of "(always) one column per clus index" at the expense of the genuine performance of your dbs.
Adding another column to a unique index will allow duplicates to the columns that are supposed to be unique. That's not a performance issue, that's a data integrity issue.
If defining a set of columns as unique, that index should have just the unique columns defined in it, otherwise the unique constraint is not doing the job of enforcing uniqueness across a set of columns.
If you have a business design that says that email address column must be unique, then defining the unique index on email address, surname, status and last order date may be great for performance, but so much for the unique email address.
Look at some of MS's system tables: some of their internal replication tables have 5 clus keys.
Yeah, and many of them have horrific performance.
If you don't look up by the unique column, but by a non-unique column first and a unique column only sometimes, don't make the clus index key only the unique column. That will be a severe performance issue.
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.
MS has engineers with vastly more expertise at scale than any of us.
Sometimes multiple clus keys are needed on a table.
And since the clus index is the most important part of good query performance, you should consider it carefully and select the proper key column(s) to match your environment, not some pre-determined, overly-simplistic rule.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.