patternsqlMinor
SQL Server Indexing Performance in Unique/Duplicate column
Viewed 0 times
uniquecolumnsqlindexingduplicateperformanceserver
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#10782, answer score: 5
Revisions (0)
No revisions yet.