0

As described here clustered index in database affects how data is physically allocated on the disk.

Does it mean that if I have a huge database table and will decide that I want to change PRIMARY KEY it will lead to rearranging physical data?

How much free space will I need for this operation (if needed)?

Lets say my db file contain a single table and has size of 100 GB.

Community
  • 1
  • 1
TOP KEK
  • 2,593
  • 5
  • 36
  • 62
  • [Disk Space Requirements for Index DDL Operations](http://msdn.microsoft.com/en-us/library/ms179542(v=sql.110).aspx) – Pred Jun 19 '14 at 11:40

1 Answers1

0

For the first question: Yes, when you change the CLUSTERED INDEX, it means that the data will be rearranged. (In fact if you are alter any indexes, the data stored in the index will be rearranged to fit to the new definition).

The second part is that you can not alter any indexes, this means, that you should DROP the old index and CREATE the new one. This implies that tha data related to the old index will be deleted and the data for the new index will be stored on disk.

When you drop a CLUSTERED INDEX, thow thing will happen:

  • The table became a HEAP table, and a row identifier (RID) will be generated for each row.
  • All NONCLUSTERED INDEX will be rebuilded, since they are referencing either to the CLUSTERED INDEX key or to the HEAP RID

For the free space requirement, you can start here: http://msdn.microsoft.com/en-us/library/ms179542(v=sql.110).aspx

From that page:

Index Operations That Require Additional Disk Space

All other index DDL operations require additional temporary disk space to use during the operation, and permanent disk space to store the new index structure or structures.

When a new index structure is created, 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.

The following index DDL operations create new index structures and require additional disk space:

  • CREATE INDEX
  • CREATE INDEX WITH DROP_EXISTING
  • ALTER INDEX REBUILD
  • ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE)
  • ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE) when the constraint is based on a clustered index
  • DROP INDEX MOVE TO (Applies only to clustered indexes.)

Temporary Disk Space for Sorting

Besides the disk space required for the source and target structures, temporary disk space is required for sorting, unless the query optimizer finds an execution plan that does not require sorting.

If sorting is required, sorting occurs one new index at a time. For example, when you rebuild a clustered index and associated nonclustered indexes within a single statement, the indexes are sorted one after the other. Therefore, the additional temporary disk space that is required for sorting only has to be as large as the largest index in the operation. This is almost always the clustered index.

If the SORT_IN_TEMPDB option is set to ON, the largest index must fit into tempdb. Although this option increases the amount of temporary disk space that is used to create an index, it may reduce the time that is required to create an index when tempdb is on a set of disks different from the user database.

If SORT_IN_TEMPDB is set to OFF (the default) each index, including partitioned indexes, is sorted in its destination disk space; and only the disk space for the new index structures is required. For an example of calculating disk space, see Index Disk Space Example.

Pred
  • 8,789
  • 3
  • 26
  • 46