Would there be any scenario where it would make sense to create a unique non clustered index that includes a column that is contained in a unique clustered index?
In other words, if I have a Products table with a unique clustered index on the ProductID column. Would there be an acceptable scenario where I would need to create a unique non clustered index that includes the ProductID column in addition to a couple of other columns, or, would the mere inclusion of the unique ProductID column make creating the non clustered index unique irrelevant?
Best Answer
Yes having a column in multiple unique keys is sometimes perfectly reasonable. In the case that you gave above I'm not sure I would bother since the ProductId key is unique regardless. But let's say that you have a product table like this:
In this particular case the
ProductVendor
andProductCode
are together unique and are your primary key and clustered index. However there is an additional business rule thatProductDescription
must also be unique byProductVendor
. In this case you could create a non-clustered index onProductVendor, ProductDescription
.