DBCC DBREINDEX

Hi,
Is a DBCC DBREINDEX for a clustered index for a table the equivalent to a
CREATE INDEX ...WITH DROP_EXISTING for a clustered index for a table? In
other words does the DBCC DBREINDEX statement in this scenario bypass the
automatic recreation of any nonclustered indexes when the clustered index is
rebuilt? Also, if the clustered index is ommited in the DBCC DBREINDEX
statement will the statement begin with the clustered index then proceed to
the nonclustered indexes so that the nonclustered indexes only have to be
rebuilt once by SQL Server?
Thanks
JerryJerry,
The main determinination of whether the nonclustered indexes are rebuilt
with DBREINDEX when a clustered index is rebuilt is if the CI is unique or
not. If the CI is unique then it does not have to rebuild the NCI's. If it
is not unique it will automatically rebuild all the NCI's. This changes in
2005 by the way. It never has to rebuild the NCI's regardless of uniqueness
or not. If you issue DBREINDEX without specifying any index it will rebuild
the CI first and then all the NCI's. If the CI was not unique it will not
rebuild the NCI's twice.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7BdmxAqFHA.1032@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is a DBCC DBREINDEX for a clustered index for a table the equivalent to a
> CREATE INDEX ...WITH DROP_EXISTING for a clustered index for a table? In
> other words does the DBCC DBREINDEX statement in this scenario bypass the
> automatic recreation of any nonclustered indexes when the clustered index
> is rebuilt? Also, if the clustered index is ommited in the DBCC DBREINDEX
> statement will the statement begin with the clustered index then proceed
> to the nonclustered indexes so that the nonclustered indexes only have to
> be rebuilt once by SQL Server?
> Thanks
> Jerry
>|||That's pretty much the case.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7BdmxAqFHA.1032@.TK2MSFTNGP09.phx.gbl...
Hi,
Is a DBCC DBREINDEX for a clustered index for a table the equivalent to a
CREATE INDEX ...WITH DROP_EXISTING for a clustered index for a table? In
other words does the DBCC DBREINDEX statement in this scenario bypass the
automatic recreation of any nonclustered indexes when the clustered index is
rebuilt? Also, if the clustered index is ommited in the DBCC DBREINDEX
statement will the statement begin with the clustered index then proceed to
the nonclustered indexes so that the nonclustered indexes only have to be
rebuilt once by SQL Server?
Thanks
Jerry|||Thanks Andrew. One of the better explanations I've ever read.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OdhNP3DqFHA.2444@.TK2MSFTNGP11.phx.gbl...
> Jerry,
> The main determinination of whether the nonclustered indexes are rebuilt
> with DBREINDEX when a clustered index is rebuilt is if the CI is unique or
> not. If the CI is unique then it does not have to rebuild the NCI's. If
> it is not unique it will automatically rebuild all the NCI's. This changes
> in 2005 by the way. It never has to rebuild the NCI's regardless of
> uniqueness or not. If you issue DBREINDEX without specifying any index it
> will rebuild the CI first and then all the NCI's. If the CI was not
> unique it will not rebuild the NCI's twice.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:u7BdmxAqFHA.1032@.TK2MSFTNGP09.phx.gbl...
>