DBReindex on Clustered Idex

If the rebuild causes root node of clustered index to be changed then it is
a must for SQL Server to update(rebuild) nonclustered index no matter wich k
ind!!!
Example:
Suppose that you're library (clustered index) is in Street One, and your pat
h to the library(nonclustered) is Way 1. If library moves to Street Two ther
e is no point to go through Way 1 as we never get to Street One, but Way 2.
Eh, funny, isn't it?
More info "Indexing Architecture" BOL
Hope it HelpsThat is not true.
Non-clustered indexes do not contain physical links back to the clustered
index, just logical links (i.e. the cluster keys). The root node is only
stored in database metadata and so is irrelevant to this discussion.
For a unique clustered index, the cluster keys are exactly as defined by the
table schema and rebuilding a clustered index does not change any of the
cluster keys so none of the non-clustered indexes must be rebuilt.
For a non-unique clustered index, an artificial uniquifier column is added
to the defined cluster keys to give a unique logical identifier for each row
in the clustered index. Non-clustered indexes over a non-unique clustered
index have to include the uniquifier column as part of the logical link back
to the clustered index. The uniquifier is regenerated when the non-unique
clustered index is rebuilt, so if any non-clustered indexes must also be
rebuilt to pick up the new uniquifier values.
SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
se, but a performance drain. The problem was fixed in SP2.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sinisa Perovic" <anonymous@.discussions.microsoft.com> wrote in message
news:E493EEE6-A7EF-4BB9-8C49-08F6B54DB347@.microsoft.com...

quote:

---

> If the rebuild causes root node of clustered index to be changed then it

---

is a must for SQL Server to update(rebuild) nonclustered index no matter
wich kind!!!

quote:

---

> Example:
> Suppose that you're library (clustered index) is in Street One, and your

---

path to the library(nonclustered) is Way 1. If library moves to Street Two
there is no point to go through Way 1 as we never get to Street One, but Way
2.

quote:

---

> Eh, funny, isn't it?
> More info "Indexing Architecture" BOL
> Hope it Helps

---

|||Paul,
if a non-unique clustered index contains no duplicate keys, will other
indexes still be reindexed, or is SQL-Server smart enough to only
reindex after finding a duplicate in the clustered index tree?
Thanks,
Gert-Jan
"Paul S Randal [MS]" wrote:

quote:

---

> That is not true.
> Non-clustered indexes do not contain physical links back to the clustered
> index, just logical links (i.e. the cluster keys). The root node is only
> stored in database metadata and so is irrelevant to this discussion.
> For a unique clustered index, the cluster keys are exactly as defined by t
he
> table schema and rebuilding a clustered index does not change any of the
> cluster keys so none of the non-clustered indexes must be rebuilt.
> For a non-unique clustered index, an artificial uniquifier column is added
> to the defined cluster keys to give a unique logical identifier for each r
ow
> in the clustered index. Non-clustered indexes over a non-unique clustered
> index have to include the uniquifier column as part of the logical link ba
ck
> to the clustered index. The uniquifier is regenerated when the non-unique
> clustered index is rebuilt, so if any non-clustered indexes must also be
> rebuilt to pick up the new uniquifier values.
> SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
> se, but a performance drain. The problem was fixed in SP2.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine

---

<snip>|||They're always rebuilt and with respect, it's not a question of smart
enough.
The problem is that rebuilding the clustered index will reset all the
uniquifiers - so if the clustered index _used_ to have duplicates but
doesn't any more, the uniquifier value for the remaining unique values will
change, making the non-clustered index out of date. You could do the
theoretically do the sort using the old uniquifier values but that's very
nasty.
The alternative is to track whether a duplicate exists which is very
difficult to do and keep perf in any way respectable (think of the checks
you'd have to run for a particular key value on each update or delete).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FFF035B.F8D22897@.toomuchspamalready.nl...

quote:

---

> Paul,
> if a non-unique clustered index contains no duplicate keys, will other
> indexes still be reindexed, or is SQL-Server smart enough to only
> reindex after finding a duplicate in the clustered index tree?
> Thanks,
> Gert-Jan
>
> "Paul S Randal [MS]" wrote:
clustered[QUOTE]
the[QUOTE]
added[QUOTE]
row[QUOTE]
clustered[QUOTE]
back[QUOTE]
non-unique[QUOTE]
per[QUOTE]
> <snip>

---

|||I see. I agree that it is not worth the trouble (and overhead) to do it
any other way.
Thanks for the information.
Gert-Jan
"Paul S Randal [MS]" wrote:[QUOTE]
> They're always rebuilt and with respect, it's not a question of smart
> enough.
> The problem is that rebuilding the clustered index will reset all the
> uniquifiers - so if the clustered index _used_ to have duplicates but
> doesn't any more, the uniquifier value for the remaining unique values wil
l
> change, making the non-clustered index out of date. You could do the
> theoretically do the sort using the old uniquifier values but that's very
> nasty.
> The alternative is to track whether a duplicate exists which is very
> difficult to do and keep perf in any way respectable (think of the checks
> you'd have to run for a particular key value on each update or delete).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> clustered
> the
> added
> row
> clustered
> back
> non-unique
> per