what is the difference between the drop and create the index and rebuild index ?

Question

Hi All,

what is the difference between drop and create index & rebuild index ? i think both are same...Please clarify if both are same or any difference...

Thanks in Advance,

---

rup

Answer 1

Yes, they both are same.

BOL : Rebuilding an index drops and re-creates the index.

Reference: https://msdn.microsoft.com/en-us/library/ms189858.aspx?f=255&MSPPError=-2147217396

Use REBUILD when average fragmenation is more that is more than 30%.

---

-Vaibhav Chaudhari

Answer 2

see

https://sqlinbuddy.wordpress.com/2010/09/14/recreate-or-rebuild-indexes/

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

creating index and rebuilding index internally both are same and resource intensive except Online rebuild index option will keep the table available.

Answer 4

Both are same. Rebuilding an index drops and re-creates the index. 

Ref:

SSMS - https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx

TSQL - https://msdn.microsoft.com/en-us/library/ms188388.aspx

I would suggest you to also refer one of the best index maintenance script as below:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Answer 5

There is one important difference between the two. Rebuild is implemented as created, followed by drop (not drop followed by create). So if you have a 10GB index, for instance, you need to GB free space in the database for a rebuild.

Rebuild is easier to manage, of course (automate, script etc). And it is one transaction: if anything fails, then both internal operations are rolled back.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 6

In addition to the other posts:

For a clustered index, there is a huge difference. If you drop a clustered
index, all the non-clustered index has to be rebuilt as well, since the
clustered key is used as row located in the NC indexes and must be replaced
with record ids (RID). And when you recreate the clustered index, the non-
clustered indes are rebuilt once more.

---

Erland Sommarskog, SQL Server MVP, [e-mail]