Creating indexes requires space to store the sorted index. For clustered indexes, this requires copying the data rows to new locations in the order of the index key. The newly ordered data rows and the upper levels of the index must be written before the base table can be removed. Unless you are using the with sorted_data clause to suppress the sort, creating a clustered index requires approximately 120 percent of the space occupied by the table.
Creating a nonclustered index requires space to store the new index. To help determine the size of objects and the space that is available, use the following system procedures:
sp_spaceused – to see the size of the table. See “Using sp_spaceused to display object size” on page 250 in Performance and Tuning: Basics.
sp_estspace – to predict the size of the index. See “Using sp_estspace to estimate object size” on page 252 in Performance and Tuning: Basics.
sp_helpsegment – to see space left on a database segment. See “Checking data distribution on devices with sp_helpsegment” on page 114 in Performance and Tuning: Basics.
