Microsoft® SQL Server 2008 R2 Unleashed

Note

If you drop a clustered index on a table, SQL Server needs to rebuild all the remaining nonclustered indexes on the table. The reason is that when a clustered index exists on a table, the nonclustered indexes include the clustered index key in the nonclustered index rows as a pointer to the corresponding data rows. When the clustered index is dropped, the clustered index key needs to be replaced with page and row pointers. If a large number of nonclustered indexes exist on the table, the operation to rebuild the nonclustered indexes can be very time consuming and I/O intensive. For more information on the internal structures of clustered and nonclustered indexes, see Chapter 34.