SQL Server - so long to drop a clustered index - Feb 27, 2010
at 11:50 AM by Shuchi Gauri
Describe why it takes so long to drop a clustered index.
When a clustered index is dropped, the index definition is deleted from metadata
and all the data rows that were stored in the leaf level of the clustered
index. In addition to dropping the clustered index, all nonclustered indexes on
the table must be rebuilt to replace the clustered index keys with row pointers
to the heap, which takes a lot of time. To save time, when we need to drop all
indexes on a table, we must drop the nonclustered indexes first and then the
clustered indexes in the end.
SQL Server - so long to drop a clustered index - May 05, 2009
at 22:00 PM by Rajmeet Ghai
Describe why it takes so long to drop a clustered index.
A clustered index is uses the index key to sort the data of the table. And
since there can be only one sort order, there can be only one clustered index.
When the table has a clustered index, the pointers contain the clustered index
keys for that row. And hence when a clustered index is dropped, the SQL server
has to recreate non clustered indexes for that table by replacing the clustered
index keys by the RowID. This consumes a lot of time.
SQL Server - so long to drop a clustered index - June 21,
2009 at 09:00 AM by Amit Satpute
Describe why it takes so long to drop a clustered index.
-
A table can have only one cluster index which sorts the data in the table
physically. The sort order for the data that has been sorted physically cannot
vary.
-
A table with clustered index has pointers that contain the clustered index keys
for that row.
-
The drop operation is time consuming as the clustered indexes then need to be
replaced by the RowIDs for the data.
Also read
Answer - Following are the actions prevented once referential
integrity is enforced......
Answer - Index can be thought as index of the book that is used
for fast retrieval of information. Index uses one or more column index keys and
pointers to the record to locate record.........
Answer - SQL Server uses the following steps to process a
single SELECT statement....
|
