November 18, 2005 at 12:09 pm
Hi,
I have a doubt regarding the functionality for DBCC DBREINDEX V/S DBCC INDEXDEFRAG.
After executing each of these statements on Clustered Indexes, do the Non-Clustered Indexes on the same table as the clustered index we have rebuilt/defragged are also rebuilt
or do we have to rebuild these non-clustered indexes separately.
In the article http://www.sql-server-performance.com/rd_index_fragmentation.asp the author says that "DBCC DBREINDEX is similar to CREATE INDEX with DROP_EXISTING" which means after executing DBREINDEX we have to rebuild the non-clustered indexes separately.
Here is an example:
Suppose I have two tables T1 and T2, both having clustered and non-clustered indexes on them.
If i execute DBCC DBREINDEX on clustered index for T1 and execute DBCC INDEXDEFRAG for clustered index on T2, which table's non-clustered indexes will need to be rebuilt?
Regards
RSingh
November 18, 2005 at 12:44 pm
You should not have to re-build your non-clustered indexes manually upon rebuilding your clustered indexes. If you issue a DROP index statement on a clustered index, SQL Server will automatically rebuild all non-clusted indexes on that table as the non-clustered indexes contains the clustered index key. This recreation of the non-clustered indexes happens again when you create the clustered index again. Using the DBCCREINDEX, from my understanding, would be the same as using the DROP EXISTING clause of the create index statement. This improves efficiency in rebuilding your indexes as it only has to rebuild the non-clustered indexes once.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply