Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Stairway to SQL Server Indexes: Step 12, Create Alter Drop Expand / Collapse
Posted Thursday, February 23, 2012 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 4, 2016 7:44 AM
Points: 1, Visits: 66
This is a great series. I've enjoy'ed it a great deal. But I'm having a problem getting my head around the paragraph on disabling an index:

The primary reason for disabling an index is to save disk space when rebuilding the index. If the index has not been disabled, the rebuild process maintains the original version of the index until the new version has been created; resulting in both versions consuming disk space at the same time. By deleting the index first; an entire index’s worth of disk space is saved during the rebuild process. Rebuilding an index that has been deleted typically requires about one fifth the disk space that rebuilding an undeleted index requires.

How can you rebuild something that has been deleted?
Post #1256732
Posted Thursday, February 23, 2012 9:13 AM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
If the index is disabled, the metadata is still there. That's all that's needed to rebuild an index (rebuild actually creates an new index structure). If the index is actually deleted, then it can't be rebuild and would have to be completely recreated

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1256756
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse