Clustered Index quetion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard Douglass - 11 Mar 2008 23:02 GMT

I want to implement clustered indexes on some tables and have a couple of
questions. I read that a clustered index has its columns shared with
non-clustered indexes by default. Does that mean I can remove the columns
found in the clustered index from the non-clustered indexes?

NONCLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, InvoiceDate)

If I switch #1 to a CLUSTERED can I switch 2 and 3 to this:

CLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceDate)

??

Thanks
Richard

Reply to this Message

Tibor Karaszi - 11 Mar 2008 23:20 GMT

Yes, the clustering key is autimatically a member of each non-clustered index. But you also need to
consider the column order. For instance: below two indexes are not the same:

NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber, InvoiceNumber)

So, to take one of your examples. If you have below clustered index:

> CLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)

The below NC index:

> NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber)

Is the same as below:

NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber, InvoiceNumber)

Which is not the same as your original index:

> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)

As an aside, I find it slightly odd to have below three indexes

> NONCLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, InvoiceDate)

I would question the value of the first index. I.e., whether not the second and third are close to
as efffieient to use as the first one.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>I want to implement clustered indexes on some tables and have a couple of questions. I read that a
>clustered index has its columns shared with non-clustered indexes by default. Does that mean I can
[quoted text clipped - 14 lines]
> Thanks
> Richard

Reply to this Message