August 16, 2012 at 1:26 pm
Some Questions about Create index ... with Drop_Existing
1) in case of Create Clustered index ... with Drop_existing=on non-clustered indexes are rebuilt only once using this option or not at all if the key definition is the same.Is this done by creating another copy of the clustered index(and keeping the old one until the new clustered index is created) or it places some locks on the non-clustered index and defers rebuilding that (if necessary) until the new clustered index is created?
2)in case of Create Non-Clustered index ... with Drop_existing=on what does it exactly do?are users able to use the non-clustered index during build process?
Pooyan
August 17, 2012 at 4:20 am
Drop does exactly what it says. It's going to drop the existing index and then rebuild it. No, it won't leave the existing index available while it does the rebuild... unless you're also doing ONLINE operations with the index. Then the other index will stay in place while the create operation occurs. This will take extra tempdb space, so be ready for that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 17, 2012 at 11:38 am
So there's no difference between using DROP_EXISTING=ON,ONLINE=OFF and dropping and recreating a non-Clustered index .Is there?
Pooyan
August 17, 2012 at 11:49 am
Other than that with drop.. create there's potential for queries to use the table between the drop and the create, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2012 at 12:06 pm
Thanks
Pooyan
August 20, 2012 at 6:09 am
Grant Fritchey (8/17/2012)
Drop does exactly what it says. It's going to drop the existing index and then rebuild it. No, it won't leave the existing index available while it does the rebuild... unless you're also doing ONLINE operations with the index. Then the other index will stay in place while the create operation occurs. This will take extra tempdb space, so be ready for that.
Can u rebuild index online? I thought index rebuild must be offline, index reorg is done online. Please advise.
Thanks,
SueTons
Regards,
SQLisAwe5oMe.
August 20, 2012 at 6:28 am
Sure you can. Use the ONLINE keyword when rebuilding the index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply