dbcc dbreindex ...

Does it drop all indexes and recreate it under the hood ?
Hi,
Using DBCC DBREINDEX on a table is 100% equalent to use DROP and CREATE
Indexes. Page density will be restored based on the
fill facter associated. The only advantage in DBCC DBREINDEX command over
DROP and CREATE INDEX is : You do not have to know
anything about the underlying table structure.
Only people from SQL Server development team can answer your question
exactly.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Does it drop all indexes and recreate it under the hood ?
>
>
|||It actually recreates the new indexes first and then drops the original
ones. Of coarse it does all the proper renaming etc. for you as well. That
is why it requires 1.2 times the size of the index in free space in the data
files to operate.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Does it drop all indexes and recreate it under the hood ?
>
>
|||Actually there is one very big difference - drop/create does not guarantee
that any constraints defined are adhered to between the drop and the create
statement (e.g. key uniqueness). Doing a dbreindex does.
Also, if you're operating on a clustered index, drop and recreate is a
terrible choice - as all non-clustered indexes must be rebuilt when the
clustered index is dropped, and then rebuilt again when the clustered index
is re-created.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
> It actually recreates the new indexes first and then drops the original
> ones. Of coarse it does all the proper renaming etc. for you as well.
> That is why it requires 1.2 times the size of the index in free space in
> the data files to operate.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23hwZfspiFHA.3448@.TK2MSFTNGP12.phx.gbl...
>
|||A transaction would ensure the same consistency as drop/create though,
right?
And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the non-clustered
index rebuilds problem..
Regards,
Greg Linwood
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Actually there is one very big difference - drop/create does not guarantee
> that any constraints defined are adhered to between the drop and the
> create statement (e.g. key uniqueness). Doing a dbreindex does.
> Also, if you're operating on a clustered index, drop and recreate is a
> terrible choice - as all non-clustered indexes must be rebuilt when the
> clustered index is dropped, and then rebuilt again when the clustered
> index is re-created.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1kwt7siFHA.3672@.TK2MSFTNGP10.phx.gbl...
>
|||That still would not deal with RI. If you use DROP and CREATE you have to
manually deal with the RI first and then afterwards. DBREINDEX takes care
of that for you. The DROP_EXISTING will still rebuild the non-clustered
indexes if the clustered index was being rebuilt and is not unique.
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>A transaction would ensure the same consistency as drop/create though,
>right?
> And you can use CREATE INDEX .. WITH DROP_EXISTING to stop the
> non-clustered index rebuilds problem..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23bkRydtiFHA.1148@.TK2MSFTNGP12.phx.gbl...
>
|||What do you mean about no dealing with RI? RI wasn't in the question, but RI
maintenance can also be scripted fairly easily. I agree about non-unique
non-clustered indexes though. DBREINDEX is definitely the way to go
currently (what I mostly do)
Regards,
Greg Linwood
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234e%23Fz5iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> That still would not deal with RI. If you use DROP and CREATE you have to
> manually deal with the RI first and then afterwards. DBREINDEX takes care
> of that for you. The DROP_EXISTING will still rebuild the non-clustered
> indexes if the clustered index was being rebuilt and is not unique.
> --
> Andrew J. Kelly SQL MVP
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uLUjg14iFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
|||Well the question was about DBREINDEX but then other methods popped up<g>.
The point I was trying to make was that often people forget about RI and
attempt to drop and recreate the index and will get an error. Then if they
do drop the RI first they must remember exactly what it was to redo when
done. DBREINDEX makes that a no brainier since you don't have to remember
anything.
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eZ8Idc%23iFHA.1444@.TK2MSFTNGP10.phx.gbl...
> What do you mean about no dealing with RI? RI wasn't in the question, but
> RI maintenance can also be scripted fairly easily. I agree about
> non-unique non-clustered indexes though. DBREINDEX is definitely the way
> to go currently (what I mostly do)
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%234e%23Fz5iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>