Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 17 '12 at 12:55

This question came from our site for professional and enthusiast programmers.

    
Cardinality is one aspect that matters –  OMG Ponies Jan 17 '12 at 4:58
    
Thanks for your response, but I want to know why if cardinality is more performance of index is good. I will appreciate if anyone explain with some some data. –  Ashish Khandelwal Jan 17 '12 at 5:07

4 Answers 4

up vote 3 down vote accepted

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.

share|improve this answer
    
Agree with you but I like to know it from indexing structure of database. When we create non-clustered index on any column than each leaf node of that index store that column value with 4 bytes of pointer to point physical value of table. If value is unique or not each leaf node will occupy column value with pointer, so why index in a column with unique value gives greater performance –  Ashish Khandelwal Jan 17 '12 at 5:25
    
I'm not sure I understand your question fully. If each value of a column is unique, one leaf node will point to one record, the best performance you can get. If there are more than one record with the same value then each of those records will have to be scanned individually. I always think of a phone book. A phone book is indexed on the first letter of each name, that only divides the records into 26 sections, you then have to scan each section for the name you want so it isn't a very ideal index. –  NoxHarmonium Jan 19 '12 at 0:08

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.

share|improve this answer
    
Please share your details with physical structure of non-clustered index which include binary tree, leaf node and pointer to physical data of table. –  Ashish Khandelwal Jan 17 '12 at 5:27
    
+1 Unique indexes are more flexible than unique constraints I believe though. IIRC it is not possible to add INCLUDEd columns to a unique constraint. –  Martin Smith Jan 17 '12 at 13:34

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.