September 10, 2003 at 11:58 am
Hi Friends:
Does any one have any idea why making a clustered index as a non clustered index takes hours to execute. All I am doing is unchecking the Clustered Index check mark on the table design from SQL EM. The table is 100 GB, but my understanding is to make a table unclustered is a change in the definition. It should not have to touch each page to make the change. I understand if it takes long when adding a clustered index. But while dropping.?? Any ideas?
thanks.
September 10, 2003 at 2:05 pm
I think I understand what is going on here. When I dropped the clustered index, it has to touch all the non-clustered indexes to update the previous clustered key portion with the relavant heap data (since it has no index).. Yikes. I might have a long day to go before these are complete... sigh..
September 10, 2003 at 2:18 pm
The Clustered index is the physical order. It is also the reference value that all other indexes use. That is, all other indexes contain the clustered index key. When you drop the clustered index, the table must be transformed into a heap with a hidden ROWID value AND all the other indexes must be rebuilt using this new key. Dropping and creating a clustered index require almost the same amount of work by the SQL server.
June 3, 2005 at 7:27 am
Make sure you read closely the explanations givven in BOL under CREATE INDEX -> Creating an index. Here's the management summary:
"If a clustered index is created on a table with several secondary indexes, all of the secondary indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Likewise, if a clustered index is deleted on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables.
The preferred way to build indexes on large tables is to start with the clustered index and then build the nonclustered indexes. When dropping all indexes, drop the nonclustered indexes first and the clustered index last. That way, no indexes need to be rebuilt."
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply