SQL Server Clustered Index - Order of Index Questi

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

Just in case this isn't obvious: the sort order of your index does not promise much about the the sort order of the results in a query.

In your queries, you must still add an

ORDER BY KeyA, KeyB

or

ORDER BY KeyB, KeyA

The optimizer may be pleased to find the data already physically ordered in the index as desired and save some time, but every query that is supposed to deliver data in a particular order must have an ORDER BY clause at the end of it. Without an order by, SQL Server makes no promises with respect to the order of a recordset, or even that it will come back in the same order from query to query.

Answer 2

You specify the columns in the order in which you would normally want them sorted in reports and queries.

I would be wary of creating a multicolumn clustered index though. Depending on how wide this is, you could have a huge impact on the size of any other indexes you create because all non-clustered indexes contain the clustered index value in them. Also the rows have to be re-ordered if the values frequently change and it is my experience that non-surrogate keys tend to change more frequently. Therefore creating this as a clustered vice nonclustered index could be much more time consuming of server resources if you have values that are likely to change. I'm not saying you shouldn't do this as I don't know what type of data your columns actually contain (although I suspect they are more complex that A1, a2, etc); I'm saying you need to think about the ramifications of doing it. It would probably be a good idea to thoroughly read BOL about clustered vice nonclustered indexes before committing to doing this.

Answer 3

Yes you should suggest, normally query engine try to find out the best execution plan and the index to utilize, however sometime it is better to force query engine to use the specific index. There are some other consideration when planning for index as well as when utilizing the index in your query. for example, the column ordering in index, column ordering in where clause. you could refer following link to know about:

http://ashishkhandelwal.arkutil.com/sql-server/quick-and-short-database-indexes/

• Best Practices to use indexes
• How to get best performance form indexes
• Clustered index Considerations
• Nonclustered Indexes Considerations

I am sure this will help you when planning for index.