Clustered Index quetion

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
RichardYes, 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.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:OmVuXO8gIHA.3940@.TK2MSFTNGP05.phx.gbl...
>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
>