sql server - Rearranging data on changing clustered index in a huge table -

sql server - Rearranging data on changing clustered index in a huge table -

as described here clustered index in database affects how info physically allocated on disk.

does mean if have huge database table , decide want alter primary key lead rearranging physical data?

how much free space need operation (if needed)?

lets db file contain single table , has size of 100 gb.

for first question: yes, when alter clustered index, means info rearranged. (in fact if alter indexes, info stored in index rearranged fit new definition).

the sec part can not alter indexes, means, should drop old index , create new one. implies tha info related old index deleted , info new index stored on disk.

when drop clustered index, thow thing happen:

the table became heap table, , row identifier (rid) generated each row. all nonclustered index rebuilded, since referencing either clustered index key or heap rid

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

from page:

index operations require additional disk space

all other index ddl operations require additional temporary disk space utilize during operation, , permanent disk space store new index construction or structures.

when new index construction created, disk space both old (source) , new (target) structures required in appropriate files , filegroups. old construction not deallocated until index creation transaction commits.

the next index ddl operations create new index structures , require additional disk space:

create index create index drop_existing alter index rebuild alter table add together constraint (primary key or unique) alter table drop constraint (primary key or unique) when constraint based on clustered index drop index move (applies clustered indexes.)

temporary disk space sorting

besides disk space required source , target structures, temporary disk space required sorting, unless query optimizer finds execution plan not require sorting.

if sorting required, sorting occurs 1 new index @ time. example, when rebuild clustered index , associated nonclustered indexes within single statement, indexes sorted 1 after other. therefore, additional temporary disk space required sorting has big largest index in operation. clustered index.

if sort_in_tempdb alternative set on, largest index must fit tempdb. although alternative increases amount of temporary disk space used create index, may cut down time required create index when tempdb on set of disks different user database.

if sort_in_tempdb set off (the default) each index, including partitioned indexes, sorted in destination disk space; , disk space new index structures required. illustration of calculating disk space, see index disk space example.

sql-server database clustered-index