Index Cluster Vs. Index No Cluster

Hi,
it is good practices to have in a table (Hotels) Index not to cluster on the
HotelID column and Index to cluster on the foreign column HotelID in a table
details?
Thank you
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ering/200509/1
As always it depends.
Firstly what is the key, how is the key determined and what is the insert
activity?
An ascending integer key is fine to use as a clustered index but only if the
inserts are made in ascending order (1,2,3,4....)
If it's 1, 9999, 45, 77, 888, 4345,... then pages will have to split
eventually to make room for keys as the inserts proceed. SQL will put as
many keys as it can in a page (fill factors restrict this of course) so when
later on you insert a low key number then the page must be split. In this
instance it is better to have only a non-clustered index on the key. This
table is called a "heap".
If the insert activity is low then of course you may be better with a
clustered index after all.
If the inserts are 1, 3, 5, 6, 7, then each page fills up as the insertes
proceed and there is no need for splits because there are no gaps. This is
fine for a clustered index.
If the key is not a numeric but a Name or character data then do not use
this as the clustered index. This is because other indexes you create have
the primary key in their index as the target of the index so you duplicate a
long key for every additional index. In this case ceated a identifier column
for your clustered index and create a unique index on your long key.
Nik Marshall-Blank MCSD/MCDBA
"CYanez via droptable.com" <forum@.droptable.com> wrote in message
news:5467849175759@.droptable.com...
> Hi,
> it is good practices to have in a table (Hotels) Index not to cluster on
> the
> HotelID column and Index to cluster on the foreign column HotelID in a
> table
> details?
> Thank you
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ering/200509/1
|||Thank you Nik
Nik Marshall-Blank wrote:[vbcol=seagreen]
>As always it depends.
>Firstly what is the key, how is the key determined and what is the insert
>activity?
>An ascending integer key is fine to use as a clustered index but only if the
>inserts are made in ascending order (1,2,3,4....)
>If it's 1, 9999, 45, 77, 888, 4345,... then pages will have to split
>eventually to make room for keys as the inserts proceed. SQL will put as
>many keys as it can in a page (fill factors restrict this of course) so when
>later on you insert a low key number then the page must be split. In this
>instance it is better to have only a non-clustered index on the key. This
>table is called a "heap".
>If the insert activity is low then of course you may be better with a
>clustered index after all.
>If the inserts are 1, 3, 5, 6, 7, then each page fills up as the insertes
>proceed and there is no need for splits because there are no gaps. This is
>fine for a clustered index.
>If the key is not a numeric but a Name or character data then do not use
>this as the clustered index. This is because other indexes you create have
>the primary key in their index as the target of the index so you duplicate a
>long key for every additional index. In this case ceated a identifier column
>for your clustered index and create a unique index on your long key.
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ering/200509/1