Learn T-SQL Querying

Unique versus non-unique

We already discussed why uniqueness is important for clustered indexes, but uniqueness is important for non-clustered indexes as well. In many cases, non-clustered indexes are not unique, but if they are, it's a good idea to specify the UNIQUE keyword. Not only does this help with the integrity of our data, it also makes for a more efficient index structure.

As with clustered indexes, SQL Server needs some way to uniquely identify each row of a non-clustered index. If the non-clustered index key is not unique by definition, SQL Server will add the clustered index key (including the uniqueifier if one exists), or the row Identifier (RID) if the table is a heap, to the non-clustered index key in order to make it unique. ...