Hi

I have two questions about indexes:

I found the following statement "...all other (non-clustered) indexes of the index table contain values of the clustering key..." (http://msdn.microsoft.com/en-us/library/aa964133(SQL.90).aspx). Does this mean I have all fields from a clustered index available in any other index? Thus I don't have to include "clustered fields" in any other indexes?

My "master-table" has about 70 fields. At the moment the clustered index is just the primary key (an ever-increasing id). My table contains an other Id called GroupId which groups records (rows) together. Almost every query is using this GroupId (many queries join over GroupId which is an FK in many other tables). Should I extend (recreate)my clustered index over this field? If yes, should I create an clustered index (Id, GroupId) or (GroupId, Id)?

Any help is appreciated.

Thank you!

Hi Dunken,

Good questions! To answer your first question, the record locator for a non-clustered index is the clustered index value. So if you were to execute a query like...

[begin pseudo code]

Select ClusteredIndexColumn
From myTable
Where nonClusteredIndexColumn = myValue;

[/end pseudo code]

... you should see an index seek being performed without any bookmark lookups. There are some exceptions to this, however, i.e. this blog article by Kalen Delaney.

The best way to understand is to run some tests and look at the query plan. Perhaps this will help get you started:

The answer to your second question is a little more difficult. The short answer is, "it depends." I'm assuming your clustered index is your ID column because your GroupId column is nonsequential and/or nonunique?

A well-formed nonclustered index on your GroupId column should perform well, and you can use included columns to help cover your index. In fact, you may find that the query optimizer would choose a non-clustered index on GroupId over a composite clustered index on (ID, GroupId); run some tests in a DEV environment and see what happens. If you do decide to make the change to your clustered index, it's typically recommended to put the most selective (unique) value first in a clustered index, followed by the next most unique, and so on. Here's a good blog article if you're interested in more detail.

---

Michelle Ufford | SQLFool.com | Please mark solved if I've answered your question :)

Hi Dunken,

Good questions! To answer your first question, the record locator for a non-clustered index is the clustered index value. So if you were to execute a query like...

[begin pseudo code]

Select ClusteredIndexColumn
From myTable
Where nonClusteredIndexColumn = myValue;

[/end pseudo code]

... you should see an index seek being performed without any bookmark lookups. There are some exceptions to this, however, i.e. this blog article by Kalen Delaney.

The best way to understand is to run some tests and look at the query plan. Perhaps this will help get you started:

The answer to your second question is a little more difficult. The short answer is, "it depends." I'm assuming your clustered index is your ID column because your GroupId column is nonsequential and/or nonunique?

A well-formed nonclustered index on your GroupId column should perform well, and you can use included columns to help cover your index. In fact, you may find that the query optimizer would choose a non-clustered index on GroupId over a composite clustered index on (ID, GroupId); run some tests in a DEV environment and see what happens. If you do decide to make the change to your clustered index, it's typically recommended to put the most selective (unique) value first in a clustered index, followed by the next most unique, and so on. Here's a good blog article if you're interested in more detail.

---

Michelle Ufford | SQLFool.com | Please mark solved if I've answered your question :)

As mentioned above, you can benefit by using Included columns to cover your query and avoid the lookup operation. I tested few things on included columns and index usage. You can check it here. With respect to Included columns you can improve the performance but ensure that the index size doesn't become too large. Check this as well.

- Deepak

---

Deepak | Mark the answers if it helps to solve your problem |

Thank you for your support.

>>I'm assuming your clustered index is your ID column because your GroupId column is nonsequential and/or nonunique?
Yes, that's exactly the reason!