Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Clustering / September 2005

Tip: Looking for answers? Try searching our database.

Index Cluster Vs. Index No Cluster

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
CYanez - 15 Sep 2005 15:02 GMT
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
Nik Marshall-Blank - 23 Sep 2005 13:32 GMT
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.
Signature

Nik Marshall-Blank MCSD/MCDBA

> Hi,
> it is good practices to have in a table (Hotels) Index not to cluster on
[quoted text clipped - 4 lines]
>
> Thank you
Carlos Y - 26 Sep 2005 16:35 GMT
Thank you Nik

>As always it depends.
>
[quoted text clipped - 27 lines]
>>
>> Thank you
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.