April 9, 2009 at 8:51 am
Hi all,
I was reading the BOL "Disk Space Requirements for Index DDL Operations " to understand how much space i need to create a index. But have some doubts :
The BOL Says :
"Whenever an index is created, rebuilt, or dropped, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits"
In example the table was a heap and alread have 2 nonclustered index and will add a clustered index. Ok...The RID of nonclustered index will be change by Clustered Key
Question 1
But if was another nonclustered index (my table still a heap).Do i need space for both structures yet ? (old and new)
Question 2
But if was another nonclustered index and the table already have a clustered index. Do in need space for both structures yet ? (old and new)
Another doubt : BOL SAYS
Disk Space Calculations for an Online Clustered Index Operation
When you create, drop, or rebuild a clustered index online, additional disk space is required to build and maintain a temporary mapping index. This temporary mapping index contains one record for each row in the table, and its contents are the union of the old and new bookmark columns.
To calculate the disk space needed for an online clustered index operation, follow the steps shown for an offline index operation and add those results to the results of the following step.
Determine space for the temporary mapping index.
In this example, the old bookmark is the row ID (RID) of the heap (8 bytes) and the new bookmark is the clustering key (24 bytes including a uniqueifier). There are no overlapping columns between the old and new bookmarks.
Temporary mapping index size = 1 million * (8 bytes + 24 bytes) / 80% ~ 40 MB.
OK..but if was a nonclustered index..my table stiil a heap . In my mind i think(in this example) it is s only i take off "+24 bytes"..
Its correct ?
Thanks a lot
April 9, 2009 at 9:00 am
Please don't cross post.
Answer here: http://www.sqlservercentral.com/Forums/Topic693861-146-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply