SQL Server Indexing Performance in Unique/Duplicate column

Question

It is said that if we create index on a column with more uniqueness than performance of that index will be more. But I believe that whether it is unique or not it will occupy same no. of blocks than why due to uniqueness it will be faster.

Answer 1

The more unique the key is, the more you filter the data. For example if you had an index on a field with only 2 unique values such as gender, the index would only split the search space in half or the number of records / 2.

Choosing to index a field with a greater amount of unique records (cardinality) will divide the search space further until you reach a unique key where every key maps to one record, which is the most efficient index.

Answer 2

Performance does not necessarily increase if an index is marked unique, though there are many good reasons to make an index unique if it is guaranteed to be so. One consideration is integrity: IDENTITY columns should always have a unique index or constraint, for example, since this column property does not enforce uniqueness in itself, but most database designs would expect that guarantee to exist. UNIQUEIDENTIFIER is another type that does not self-enforce uniqueness, but if used as a key, then that uniqueness must be explicitly enforced. The same argument applies to any candidate key of a relation, of course.

Uniqueness has the biggest potential for performance gains in the query optimizer. Uniqueness guarantees allow many simplifications to be applied, and these usually result in 'better' execution plans. In the best case, a uniqueness guarantee might allow entire operations to be 'optimized away', which will usually benefit performance markedly.

The storage engine can also benefit from uniqueness, even though physical storage size may be unaffected either way. Take the common example of an equality seek on an index. If the index is constrained to be unique, the storage engine can perform a singleton seek: knowing that at most only a single value can be returned allows certain physical optimizations to be applied.

Where an index is not defined as unique, the storage engine must scan (forward or backward) from the starting point to ensure it returns all duplicated values. Performance testing shows that singleton seeks on a unique index can be 30-40% faster than the seek + range scan that occurs on the same data and non-unique-index. The situation is not entirely clear-cut however, if SQL Server uses linear interpolation search on a unique index with an unfortunate data distribution, performance can be 70% worse (on 64-bit systems).

Perhaps the biggest hidden cost to uniqueness is the cost of enforcing it. The storage engine always needs to perform extra work to check for uniqueness violations when modifying a unique index. Overall, my advice is still to enforce uniqueness wherever it logically exists, but to also be aware of the downsides of uniqueness too.

Answer 3

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumbercolumn.

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.

Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. By doing this the objective of the index will be clear.

Answer 4

To the performance of index affect following things:

• number of indexed records
• wideness of indexed records
• cardinality (degree of uniqueness) of indexed columns

So, the more versatile values indexed field will have in all indexed rows - the less work will be performed by Sql Server's optimizer to find the hit