Cluster/Non-Cluster Indexes

You create the cluster index on the primary key (composite or non-composite
key), is there any reason that you would create a non-cluster index with any
fields from
the cluster index?
Is there documentation to support this answer.
Thank You,Not really. Since non-clustered indexes always point back to the clustered
index, they include the clustered key anyway. Explicitly including it would
be redundant and redundant.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D2CF11AB-6025-4080-9649-517D3CADD852@.microsoft.com...
> You create the cluster index on the primary key (composite or
> non-composite
> key), is there any reason that you would create a non-cluster index with
> any
> fields from
> the cluster index?
> Is there documentation to support this answer.
> Thank You,
>|||There are some valid reasons for explicitly including the clustered key in
the nc index definition. I wrote a whole article about it for SQL Server
Magazine. The reasons include:
self documentation
guarantee of the columns in the nc index even if the clustered is dropped or
redefined
ability to control the order of the columns
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uwNWHDe%23FHA.912@.TK2MSFTNGP11.phx.gbl...
> Not really. Since non-clustered indexes always point back to the
> clustered index, they include the clustered key anyway. Explicitly
> including it would be redundant and redundant.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:D2CF11AB-6025-4080-9649-517D3CADD852@.microsoft.com...
>> You create the cluster index on the primary key (composite or
>> non-composite
>> key), is there any reason that you would create a non-cluster index with
>> any
>> fields from
>> the cluster index?
>> Is there documentation to support this answer.
>> Thank You,
>>
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23OJ%23Zge%23FHA.2324@.TK2MSFTNGP11.phx.gbl...
> There are some valid reasons for explicitly including the clustered key in
> the nc index definition. I wrote a whole article about it for SQL Server
> Magazine. The reasons include:
> self documentation
> guarantee of the columns in the nc index even if the clustered is dropped
> or redefined
> ability to control the order of the columns
>
The last one is the big one. If you have a clustered index on (A,B,C) you
won't be able to quickly look up a row by its value of C.
I can think of two common cases of the top of my head where this applies.
First is a linking table. You want to be able to traverse the link in
either direction. The composite PK takes care of one direction, but a
secondary index on the trailing column in the PK is needed for the other
direction.
create table T(ID int primary key)
create table Y(ID int primary key)
create table TY
(
TID int references T,
YID int references Y,
constraint PK_TY primary key clustered (TID,YID)
)
create index IX_TY_YID on TY(YID)
The other is the case where the traling column in the key is a candidate
key. This is often done when you want to favor an access path using a
foreign key value. EG:
create table Orders(ID int primary key)
create table OrderItems
(
OrderID int references Orders,
ID int identity,
constraint PK_OrderItems primary key clustered (OrderID, ID)
)
create unique index IX_OrderItems_ID on OrderItems(ID)
David|||Do you know what month the article was published in SQL Magazine you would
created a non-cluster index with any fields from the cluster index?
Thank You,
> You create the cluster index on the primary key (composite or non-composite
> key), is there any reason that you would create a non-cluster index with any
> fields from
> the cluster index?
> Is there documentation to support this answer.
> Thank You,
>|||February, 2005
http://www.windowsitpro.com/Article/ArticleID/44807/44807.html
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:32DE2FF1-00BD-4DBA-9049-E3FF685FB7C7@.microsoft.com...
> Do you know what month the article was published in SQL Magazine you would
> created a non-cluster index with any fields from the cluster index?
> Thank You,
>
>> You create the cluster index on the primary key (composite or
>> non-composite
>> key), is there any reason that you would create a non-cluster index with
>> any
>> fields from
>> the cluster index?
>> Is there documentation to support this answer.
>> Thank You,
>>
>