Given some table with a primary key, e.g.:
CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, FirstName nvarchar(50), LastName nvarchar(50), Address nvarchar(200), Email nvarchar(260) --... )
we have a unique primary key on
Traditionally i might then need some additional covering indexes; for example to quickly find a user by either
CREATE INDEX IX_Customers_CustomerIDEmail ON Customers ( CustomerID, Email )
And these are the kinds of indexes i've created for decades.
It's not required to be unique, but it actually is
The index itself exists to avoid a table scan; it is a covering index in order to aid performance (the index is not there as a constraint to enforce uniqueness).
Today i remembered a tid-bit of information - SQL Server can use the fact that:
- a column has a foreign key constraint
- a column has a unique index
- a constraint is trusted
in order to help it optimize its query execution. In fact, from SQL Server Index Design Guide:
If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.
Given that my multi-column index contains the primary key, this composite index will de facto be unique. It's not a constraint that i particularly need SQL Server to enforce during every insert or update; but the fact is that this non-clustered index is unique.
Is there any advantage in marking this de facto unique index as actually unique?
CREATE UNIQUE INDEX IX_Customers_CustomerIDEmail ON Customers ( CustomerID, Email )
It seems to me that SQL Server could be smart enough to realize that my index already is unique by virtue of the fact that it contains the primary key.
- But perhaps it doesn't know this, and there's an advantage for the optimizer if i declare the index as unique anyway.
- Except perhaps that might now lead to slowdowns during inserts and updates, where it must perform uniqueness checks - where before it never had to before.
- Unless it knows the index is guaranteed to already be unique, because it contains the primary key.
I cannot find any guidance from Microsoft about what to do when a composite index contains the primary key.
The benefits of unique indexes include the following:
- Data integrity of the defined columns is ensured.
- Additional information helpful to the query optimizer is provided.
Should i mark a composite index as unique if it already contains the primary key? Or can SQL Server figure out this for itself?