| Author |
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
| |
| Tibor Karaszi 2005-04-22, 8:23 pm |
| 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@microso
ft.com...
> Hello:
>
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
>
> Thanks!
>
> Q
| |
| Alejandro Mesa 2005-04-22, 8:23 pm |
| 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@microso
ft.com...
>
>
>
| |
|
| Hi Alejandro:
Thanks!
Q
"Alejandro Mesa" wrote:
[color=darkred]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
>
> AMB
>
>
> "Q" wrote:
>
| |
| Robert Klemme 2005-04-25, 7:23 am |
|
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.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 2005-04-25, 7:23 am |
| > 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@hotmai
l.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
>
| |
| Robert Klemme 2005-04-25, 11:23 am |
|
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.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...
schrieb[color=darkre
d]
First[color=darkred]
>
>
| |
| Tibor Karaszi 2005-04-25, 11:23 am |
| > 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@do
mitilla.aioe.org...
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.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
>
|
|
|
|