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
>> >
>>
>

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 c
reate 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 m
uch 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 pro
cess so we need a
ROLLBACK, just remove the new data (as much as has been done), and the old i
s 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.g
bl...
> "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...
schrieb[vbcol=seagreen]
First[vbcol=seagreen]
>|||> 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...[
vbcol=seagreen]
> "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
>[/vbcol]

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

difference between dbreindex and drop existing clause of create index

Hi all,
Could any one of you tell me if there is any difference
between dbcc dbreindex and create index with drop_existing
option for a table with one clustered index and multiple
non clustered indexes..?
Also, please let me know what is the best way to reduce
fragmentation in tables that do not have non-clustered
indexes...?bharath,
A light view of the differences can be found at:
Rebuilding an Index
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/createdb/cm_8_des_05_271x.asp
For rebuilding several indexes I would stick with DBCC as a general rule.
The create index ... drop_existing requires that you have all the metadata
for the indexes in your script. The DBCC requires you to know nothing more
than the table that you are concerned with. As I understand it, both should
use parallel processing if available.
Russell Fields
"bharath" <barathsing@.hotmail.com> wrote in message
news:88ef01c3b57b$fdcc37d0$a601280a@.phx.gbl...
> Hi all,
> Could any one of you tell me if there is any difference
> between dbcc dbreindex and create index with drop_existing
> option for a table with one clustered index and multiple
> non clustered indexes..?
> Also, please let me know what is the best way to reduce
> fragmentation in tables that do not have non-clustered
> indexes...?