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.

Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.