Index Rebuild

Chaitanya Kiran 776 Reputation points
2024-07-01T15:15:18.72+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,318 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Rahul Randive 9,176 Reputation points Microsoft Employee
    2024-07-01T15:37:48.57+00:00

    Hi @Chaitanya Kiran

    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!


  2. Deleted

    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

  3. MikeyQiaoMSFT-0444 1,755 Reputation points
    2024-07-02T03:28:59.5333333+00:00

    .............

    0 comments No comments

  4. MikeyQiaoMSFT-0444 1,755 Reputation points
    2024-07-02T03:34:45.3733333+00:00

    Hi,Chaitanya Kiran

    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.