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.
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