This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(304, 63%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECK%3C/text%3E%3C/svg%3E)
I read somewhere that in index rebuild, sql server first creates new index and then drops old on. Is it correct?
OR it drops the old index and then creates new index?
Hello,Chaitanya Kiran we've noticed that it's been a while since we've received your latest update. How is your issue coming along? In order to close this thread, we recommend that you choose an answer and "Accept it," as this will greatly help our work.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 79%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERR%3C/text%3E%3C/svg%3E)
When you rebuild an index in SQL Server, the process involves creating a new index and then dropping the old index.
The process of rebuilding an index involves creating a new index structure based on the existing data in the table.
Thank You!
So, what will be the name of the new index that gets created in rebuild operation?
The same as the old.
I hope the answers are helpful. If you find any of them useful, please remember to mark them as the accepted answer.
If you need any additional information, please feel free to let us know.
Thank You!
But, how can two indexes with same name exist?
Short answer: Internal trickery. :-)
Longer answer: First let's note that there are two cases, offline and online rebuild. In an offline rebuild, the index (and the table) is locked, and the fact that the index exists in two places is not a problem, since it is a transaction.
In an online rebuild, the new copy of the index is built while the old copy is still available. In this case, SQL Server needs to track somewhere that there is an index rebuild in progress. This is even more pronounced if the operation is resumable. In this case, the operation can continue after a server restart. But exactly how SQL Server handles this, I don't know. It could add an extra row to sys.indexes (or rather the underlying system table) with a flag that it is an incomplete index and some temporary name. But it could also use separate tables to track the index.
In any case, once the new version of the index has been built, SQL Server updates sys.indexes, which exhibits itself that it takes a schema-modification lock on the table.
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
.............
Refered from this document.
1.Most index types can be rebuilt online without blocking running queries or updates. 2.You can also rebuild an index by first dropping the index with the DROP INDEX statement and re-creating it with a separate CREATE INDEX statement. Performing these operations as separate statements has several disadvantages, and we do not recommend this.
When using the ALTER INDEX ... REBUILD statement ，SQL Server creates a new index structure, copies data from the old index to the new index in the background, and once the new index is ready and all the necessary data has been migrated, SQL Server will switch to the new index and discard the old one.
The reason for this is to ensure that the rebuild operation does not affect transactions that are relying on the original old index.
Of course, you can also manually drop the existing index and create it.
When the level of index fragmentation exceeds 30 percent, it's not necessary to rebuild the index; you can reorganize the index on top of the existing one.
Best Regards,
Mikey Qiao
If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.
When the level of index fragmentation exceeds 30 percent, it's not necessary to rebuild the index; you can reorganize the index on top of the existing one.
You probably mean "does not exceed". Although this number of 30% is something Paul Randall pulled out of thin air when I was pressed to give an answer.
The actual limit is very much "it depends". And in these days, the internal fragmentation is not very likely to matter at all. But the page density still is.
So, what will be the name of the new index that gets created in rebuild operation?
While an index rebuild occurs, the physical media must have enough space to store two copies of the index. When the rebuild is finished, the Database Engine deletes the original index..
In one table, it is not allowed to have indexes with the same name. During the rebuilding process, the database engine requires a separate memory space for the new index. This new index is transparent to the user, as they cannot find it through a query search. It will only replace the old index once the rebuilding is complete. The entire process takes place within a transaction, so what you mentioned about the impossibility of having indexes with the same name coexist is indeed correct.