Index rebuilds Rebuilding an index offline, using ALTER INDEX REBUILD (or the deprecated DBCC DBREINDEX in SQL Server 2000) drops the target index and rebuilds it from scratch (online index rebuilds do not drop the existing index until the end of the rebuild operation).

I read an article by Paul on index rebuild. In his blog, he mention "Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy". This is what I thought, otherwise rolling back an offline index rebuild will take a considerable time.

It seem this article states offline index rebuild will drop the existing index before creating a new index? Just wanted to clarify if offline index really do drop existing index before the new ones are created and under what circumstance will this happen. i.e. does the statement mean logical metadata are marked "deleted" but actual index still exists, etc.

Index rebuilds Rebuilding an index offline, using ALTER INDEX REBUILD (or the deprecated DBCC DBREINDEX in SQL Server 2000) drops the target index and rebuilds it from scratch (online index rebuilds do not drop the existing index until the end of the rebuild operation).

Hi Gail,I copied and paste above paragraph from this article. From reading it, i got the wrong impression that offline reindex rebuild drops target index and then rebuild, whilst online index does not drop existing index until the of index rebuild.

Index rebuilds Rebuilding an index offline, using ALTER INDEX REBUILD (or the deprecated DBCC DBREINDEX in SQL Server 2000) drops the target index and rebuilds it from scratch (online index rebuilds do not drop the existing index until the end of the rebuild operation).

Hi Gail,I copied and paste above paragraph from the article.

Urgh... looks like I missed that in tech-editing (Tony wrote this article, I checked it for errors). I will set an appropriate penance and fix it later.

Sorry, I misunderstood where you read what from. Paul is, of course, correct.