I have being reading up on index, in particular the difference between a
cluster index and non-cluster index, and how they should be used. However th
e
more I read the more
I am.Basically if anyone can advise me on this question:
- Which kind of index should I use for PRIMARY KEY column, a clustered or
non-clustered index?
- Similarly which kind of index should I use for FOREIGN KEY column, a
clustered or non-clustered index?
A background of how I become
.My understanding of clustered index is, basically rows with column value
that fit an index value are grouped together. In another words, 1 index
value, multiple rows (1-to-many relationship).
Furthermore these rows are sorted within the group itself.
Therefore when SQL Server queries the DB along a column with clustered
index, it will be able to quickly find and return all the rows.
As for non-clustered index, it is basically an index that points directly to
that row. So, 1 index value, 1 row (1-to-1 relationship "ideally").
However on one article, it recommends that primary keys should be clustered
(which is what SQL Server does by default). But on another article, it
recommends that pimary keys should be non-clustered! And that is when I
become
.Since primary keys are unique, so rows are not grouped under a clustered
index at all. SQL Server cannot find more than one row under that index. So
there is no performance value gain.
However non-clustered index is more suited to set on the primary key column.
Afterall one-index value, one primary key.
So I think it makes more sense to set non-cluster index for primary key,
while reserving the clustered index for foreign keys, especially the child
table's foreign key (back to the parent row). The power of the clustered
index should not be wasted on primary key column.
Is my understanding correct?
Could u please kindly advise. TQ very much in advance.Hi
This is very,very big and important issue.
Start with
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...ing_indexes.asp
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:BABFB6D4-09F7-482B-89F9-DF10DFCF1242@.microsoft.com...
> Hi experts,
> I have being reading up on index, in particular the difference between a
> cluster index and non-cluster index, and how they should be used. However
the
> more I read the more
I am.> Basically if anyone can advise me on this question:
> - Which kind of index should I use for PRIMARY KEY column, a clustered or
> non-clustered index?
> - Similarly which kind of index should I use for FOREIGN KEY column, a
> clustered or non-clustered index?
> A background of how I become
.> My understanding of clustered index is, basically rows with column value
> that fit an index value are grouped together. In another words, 1 index
> value, multiple rows (1-to-many relationship).
> Furthermore these rows are sorted within the group itself.
> Therefore when SQL Server queries the DB along a column with clustered
> index, it will be able to quickly find and return all the rows.
> As for non-clustered index, it is basically an index that points directly
to
> that row. So, 1 index value, 1 row (1-to-1 relationship "ideally").
> However on one article, it recommends that primary keys should be
clustered
> (which is what SQL Server does by default). But on another article, it
> recommends that pimary keys should be non-clustered! And that is when I
> become
.> Since primary keys are unique, so rows are not grouped under a clustered
> index at all. SQL Server cannot find more than one row under that index.
So
> there is no performance value gain.
> However non-clustered index is more suited to set on the primary key
column.
> Afterall one-index value, one primary key.
> So I think it makes more sense to set non-cluster index for primary key,
> while reserving the clustered index for foreign keys, especially the child
> table's foreign key (back to the parent row). The power of the clustered
> index should not be wasted on primary key column.
> Is my understanding correct?
> Could u please kindly advise. TQ very much in advance.
