SQL Server Clustered Index - Order of Index Question

Question

I have a table like so:

keyA keyB data

keyA and keyB together are unique, are the primary key of my table and make up a clustered index.

There are 5 possible values of keyB but an unlimited number of possible values of keyA,. keyB generally increments.

For example, the following data can be ordered in 2 ways depending on which key column is ordered first:

keyA keyB data
A    1    X
B    1    X
A    3    X
B    3    X
A    5    X
B    5    X
A    7    X
B    7    X

or

keyA keyB data
A    1    X
A    3    X
A    5    X
A    7    X
B    1    X
B    3    X
B    5    X
B    7    X

Do I need to tell the clustered index which of the key columns has fewer possible values to allow it to order the data by that value first? Or does it not matter in terms of performance which is ordered first?

Answer 1

You should order your composite clustered index with the most selective column first. This means the column with the most distinct values compared to total row count.

"B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table." http://www.akadia.com/services/ora_index_selectivity.html?

This article is for Oracle, but still relevant.

Also, if you have a query that runs constantly and returns few fields, you may consider creating a composite index that contains all the fields - it will not have to access the base table, but will instead pull data from the index.

ligget78's comment on making sure to mention the first column in a composite index is important to remember.