SQL Server Optimization Checklist - SQL Server Query Performance Tuning - page 537

The nonclustered index does not include the UNIQUE constraint. Therefore, although the [Name] column contains
unique values, the absence of the UNIQUE constraint from the nonclustered index does not provide this information to
the optimizer in advance. Now, let's consider the performance impact of the UNIQUE constraint (or a missing UNIQUE
constraint) on the following SELECT statement:
SELECT DISTINCT
(p.[Name])
FROM Production.Product AS p;
Figure 26-1 shows the execution plan of this SELECT statement.
Figure 26-1. An execution plan with no UNIQUE constraint on the [Name] column
From the execution plan, you can see that the nonclustered AK_ProductName index is used to retrieve the data,
and then a Stream Aggregate operation is performed on the data to group the data on the [Name] column so that the
duplicate [Name] values can be removed from the final result set. Note that the Stream Aggregate operation would
not have been required if the optimizer had been told in advance about the uniqueness of the [Name] column. You
can accomplish this by defining the nonclustered index with a UNIQUE constraint, as follows:
CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_Name]
ON [Production].[Product]([Name] ASC)
WITH (
DROP_EXISTING = ON)
ON [PRIMARY];
GO
Figure 26-2 shows the new execution plan of the SELECT statement.
Figure 26-2. An execution plan with a UNIQUE constraint on the [Name] column
In general, the entity-integrity constraints (in other words, primary keys and unique constraints) provide useful
information to the optimizer about the expected results, assisting the optimizer in generating efficient execution
plans. Of note is the fact that sys.dm_db_index_usage_stats doesn't show when a constraint check has been run
against the index that defines the unique constraint.