difference between rebuild index and drop/recreate index

Hello:
What is the difference between rebuilding index (whether clustered or non
clustered) and dropping & recreating it?
Thanks!
QSame thing. In the end, the same code in SQL Server is executed. First the create code, then the
drop code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q" <Q@.discussions.microsoft.com> wrote in message
news:466A4880-AA9B-47A7-8EE8-63B81C809EBE@.microsoft.com...
> Hello:
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
> Thanks!
> Q|||There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"Q" wrote:
> Hello:
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
> Thanks!
> Q|||Hello Tibor:
Thanks!
Q
"Tibor Karaszi" wrote:
> Same thing. In the end, the same code in SQL Server is executed. First the create code, then the
> drop code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:466A4880-AA9B-47A7-8EE8-63B81C809EBE@.microsoft.com...
> > Hello:
> >
> > What is the difference between rebuilding index (whether clustered or non
> > clustered) and dropping & recreating it?
> >
> > Thanks!
> >
> > Q
>
>|||Hi Alejandro:
Thanks!
Q
"Alejandro Mesa" wrote:
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
>
> "Q" wrote:
> > Hello:
> >
> > What is the difference between rebuilding index (whether clustered or non
> > clustered) and dropping & recreating it?
> >
> > Thanks!
> >
> > Q|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
> Same thing. In the end, the same code in SQL Server is executed. First
the create code, then the
> drop code.
Hmmm, you sure you got the order right? :-)
robert|||> Hmmm, you sure you got the order right? :-)
Yep. This is in order to be able to do a ROLLBACK. This is why you need as much free space as the
table size in order to rebuild a clustered index.
Copy the data, then drop the old data. If something goes bad during this process so we need a
ROLLBACK, just remove the new data (as much as has been done), and the old is still there. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Same thing. In the end, the same code in SQL Server is executed. First
> the create code, then the
>> drop code.
> Hmmm, you sure you got the order right? :-)
> robert
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:OJHG%23vXSFHA.3144@.tk2msftngp13.phx.gbl...
> > Hmmm, you sure you got the order right? :-)
> Yep. This is in order to be able to do a ROLLBACK. This is why you need
as much free space as the
> table size in order to rebuild a clustered index.
> Copy the data, then drop the old data. If something goes bad during this
process so we need a
> ROLLBACK, just remove the new data (as much as has been done), and the
old is still there. :-)
Ouch! /me smacks myself on the forehead.
I should've known better - sorry for the noise.
Kind regards
robert
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
schrieb
> > im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
> >> Same thing. In the end, the same code in SQL Server is executed.
First
> > the create code, then the
> >> drop code.
> >
> > Hmmm, you sure you got the order right? :-)
> >
> > robert
> >
>|||> I should've known better - sorry for the noise.
We all slip from time to time. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:d4j0bm$7ej$1@.domitilla.aioe.org...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:OJHG%23vXSFHA.3144@.tk2msftngp13.phx.gbl...
>> > Hmmm, you sure you got the order right? :-)
>> Yep. This is in order to be able to do a ROLLBACK. This is why you need
> as much free space as the
>> table size in order to rebuild a clustered index.
>> Copy the data, then drop the old data. If something goes bad during this
> process so we need a
>> ROLLBACK, just remove the new data (as much as has been done), and the
> old is still there. :-)
> Ouch! /me smacks myself on the forehead.
> I should've known better - sorry for the noise.
> Kind regards
> robert
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> schrieb
>> > im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
>> >> Same thing. In the end, the same code in SQL Server is executed.
> First
>> > the create code, then the
>> >> drop code.
>> >
>> > Hmmm, you sure you got the order right? :-)
>> >
>> > robert
>> >
>>
>