I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,
Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Showing posts with label indexes. Show all posts
Showing posts with label indexes. Show all posts
Thursday, March 22, 2012
Cluster Indexes / Non-Cluster Indexes
I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Cluster Indexes / Non-Cluster Indexes
I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
--
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
--
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Cluster Index Without Primary Key
I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.com/clustered_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.sqlsql
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.com/clustered_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.sqlsql
Cluster Index Without Primary Key
I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.c...red_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.c...red_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.
Cluster Index Without Primary Key
I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,
the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.
|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.co...ed_indexes.asp
David Gugick
Quest Software
|||that is a great article that david has given you.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,
the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.
|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.co...ed_indexes.asp
David Gugick
Quest Software
|||that is a great article that david has given you.
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
--It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
> > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> >
> > "Avoid creating clustered indexes on identity columns.
> > Clustered indexes perform better on range queries,
> > such as a date. When you have a clustered index on
> > an identity column, you risk your data receiving hot
> > spots, which are caused by many people updating the
> > same data page."
> >
> > I would like to know what is the consenus in this forum on this.
> >
> > --
> >
> >
> >
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> > I prefer the exact opposite. I think hot spots (up to a point) are a
good
> > thing. Hot spotting data gives the cache manager something to grab hold
> of.
> > Clustering on an Identity column also guarantees inserts are at the end
of
> > the table, thus avoiding the dreaded page split. Hot spotting was very
> bad
> > under older versions of SQL, but SQL 2000 copes with it pretty well.
> > Clustering on a narrow unique key also has benefits, especially if you
> have
> > a lot of non-clustered indexes on a table.
> >
> > Having said that, I do not use the identity column as my Primary Key. I
> > create a PK constraint using a non-clustered index on a natural key.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> > news:2unifgF2c9ng0U1@.uni-berlin.de...
> > > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> > >
> > > "Avoid creating clustered indexes on identity columns.
> > > Clustered indexes perform better on range queries,
> > > such as a date. When you have a clustered index on
> > > an identity column, you risk your data receiving hot
> > > spots, which are caused by many people updating the
> > > same data page."
> > >
> > > I would like to know what is the consenus in this forum on this.
> > >
> > > --
> > >
> > >
> > >
> >
> >
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
--It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
> > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> >
> > "Avoid creating clustered indexes on identity columns.
> > Clustered indexes perform better on range queries,
> > such as a date. When you have a clustered index on
> > an identity column, you risk your data receiving hot
> > spots, which are caused by many people updating the
> > same data page."
> >
> > I would like to know what is the consenus in this forum on this.
> >
> > --
> >
> >
> >
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> > I prefer the exact opposite. I think hot spots (up to a point) are a
good
> > thing. Hot spotting data gives the cache manager something to grab hold
> of.
> > Clustering on an Identity column also guarantees inserts are at the end
of
> > the table, thus avoiding the dreaded page split. Hot spotting was very
> bad
> > under older versions of SQL, but SQL 2000 copes with it pretty well.
> > Clustering on a narrow unique key also has benefits, especially if you
> have
> > a lot of non-clustered indexes on a table.
> >
> > Having said that, I do not use the identity column as my Primary Key. I
> > create a PK constraint using a non-clustered index on a natural key.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> > news:2unifgF2c9ng0U1@.uni-berlin.de...
> > > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> > >
> > > "Avoid creating clustered indexes on identity columns.
> > > Clustered indexes perform better on range queries,
> > > such as a date. When you have a clustered index on
> > > an identity column, you risk your data receiving hot
> > > spots, which are caused by many people updating the
> > > same data page."
> > >
> > > I would like to know what is the consenus in this forum on this.
> > >
> > > --
> > >
> > >
> > >
> >
> >
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of[vbcol=seagreen]
> bad
> have
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of[vbcol=seagreen]
> bad
> have
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>
|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding[vbcol=seagreen]
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of
> bad
> have
>
|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com
|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>
|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding[vbcol=seagreen]
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of
> bad
> have
>
|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com
|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Tuesday, March 20, 2012
Cluster and Non-Cluster Indexes
How are cluster and non-cluster indexes data moved with SQL Server 2000
Transaction Replication? I have one database that I am using to replication
to another server.
I would like to execute the dbcc reindex job on the subscriber database to
reindex all of the indexes, would this be replicated to the subscriber
database.
Thanks,
No, it isn't - it is logged though and this does affect the log reader
agent's performance.
You can replicate these commands to the subscriber using sp_addscriptexec if
all of your subscribers are deployed via unc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8F9EC263-95CD-42D3-9C82-D5BB1DE169EC@.microsoft.com...
> How are cluster and non-cluster indexes data moved with SQL Server 2000
> Transaction Replication? I have one database that I am using to
replication
> to another server.
> I would like to execute the dbcc reindex job on the subscriber database to
> reindex all of the indexes, would this be replicated to the subscriber
> database.
> Thanks,
>
>
Transaction Replication? I have one database that I am using to replication
to another server.
I would like to execute the dbcc reindex job on the subscriber database to
reindex all of the indexes, would this be replicated to the subscriber
database.
Thanks,
No, it isn't - it is logged though and this does affect the log reader
agent's performance.
You can replicate these commands to the subscriber using sp_addscriptexec if
all of your subscribers are deployed via unc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8F9EC263-95CD-42D3-9C82-D5BB1DE169EC@.microsoft.com...
> How are cluster and non-cluster indexes data moved with SQL Server 2000
> Transaction Replication? I have one database that I am using to
replication
> to another server.
> I would like to execute the dbcc reindex job on the subscriber database to
> reindex all of the indexes, would this be replicated to the subscriber
> database.
> Thanks,
>
>
Monday, March 19, 2012
Clus. Index keys
Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> > Hi,
> >
> > I read in one sql document tht--If the table have a clustered index, the
> > bookmarks of all non-clustered indexes will have clustering keys for each
> > row, and physically moving a row on disk would of course not have any
> > effect
> > on these.
> >
> > My question is wht is getting stored in clustered index keys which is
> > independent of data row location(as it can be inferred from above tht it
> > doesnt affect non clus. index bookmarks).
> >
> > Thanks in advance.
> > Manu Jaidka
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>> Manu,
>> What is stored in the non-clustered key is the value of the associated
>> clustered key.
>> Therefore, searching a non-clustered index results in the clustered index
>> key, after which the clustered index is searched to find the row.
>> One side effect of this is that the size of the non-clustered index is
>> affected by the size of the clustered index, so keeping the cluster as
>> small
>> as is reasonable is a good idea.
>> RLF
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>> > Hi,
>> >
>> > I read in one sql document tht--If the table have a clustered index,
>> > the
>> > bookmarks of all non-clustered indexes will have clustering keys for
>> > each
>> > row, and physically moving a row on disk would of course not have any
>> > effect
>> > on these.
>> >
>> > My question is wht is getting stored in clustered index keys which is
>> > independent of data row location(as it can be inferred from above tht
>> > it
>> > doesnt affect non clus. index bookmarks).
>> >
>> > Thanks in advance.
>> > Manu Jaidka
>>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> > Hi,
> >
> > I read in one sql document tht--If the table have a clustered index, the
> > bookmarks of all non-clustered indexes will have clustering keys for each
> > row, and physically moving a row on disk would of course not have any
> > effect
> > on these.
> >
> > My question is wht is getting stored in clustered index keys which is
> > independent of data row location(as it can be inferred from above tht it
> > doesnt affect non clus. index bookmarks).
> >
> > Thanks in advance.
> > Manu Jaidka
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>> Manu,
>> What is stored in the non-clustered key is the value of the associated
>> clustered key.
>> Therefore, searching a non-clustered index results in the clustered index
>> key, after which the clustered index is searched to find the row.
>> One side effect of this is that the size of the non-clustered index is
>> affected by the size of the clustered index, so keeping the cluster as
>> small
>> as is reasonable is a good idea.
>> RLF
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>> > Hi,
>> >
>> > I read in one sql document tht--If the table have a clustered index,
>> > the
>> > bookmarks of all non-clustered indexes will have clustering keys for
>> > each
>> > row, and physically moving a row on disk would of course not have any
>> > effect
>> > on these.
>> >
>> > My question is wht is getting stored in clustered index keys which is
>> > independent of data row location(as it can be inferred from above tht
>> > it
>> > doesnt affect non clus. index bookmarks).
>> >
>> > Thanks in advance.
>> > Manu Jaidka
>>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
Clus. Index keys
Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as sma
ll
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as sma
ll
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
Clus. Index keys
Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu Jaidka
Manu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>
|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu Jaidka
Manu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>
|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
Subscribe to:
Posts (Atom)