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!
Q
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
|||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...
>
>
|||Hi Alejandro:
Thanks!
Q
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
>
> "Q" wrote:
|||"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...
> the create code, then the
> 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...
> 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...[vbcol=seagreen]
schrieb[vbcol=seagreen]
First
>
|||> 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...
> as much free space as the
> process so we need a
> old is still there. :-)
> Ouch! /me smacks myself on the forehead.
> I should've known better - sorry for the noise.
> Kind regards
> robert
> news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
> schrieb
> First
>
sql