Stuart M. Garner (7/28/2009)
Interesting that in the explanation for the "correct" answer it states:Creating a clustered index does involve a rebuild of all the data pages, so this will also change the metadata and free up the space previously taken by Column2.
Stated in the referenced article at
http://msdn.microsoft.com/en-us/library/ms177563.aspx
it states:
Note:
Dropping a column does not reclaim the disk space of the column. You may have to reclaim the disk space of a dropped column when the row size of a table is near, or has exceeded, its limit. Reclaim space by creating a clustered index on the table or rebuilding an existing clustered index by using ALTER INDEX [ http://msdn.microsoft.com/en-us/library/ms188388.aspx ] .
As observed by others, it appears there is more than one way to do this.
🙂
Hi Stuart,
Yes, there is more than one way to do this. I even included three ways, and you had to tick alll of them to score a point 😛
I have a hunch that you think the text you quote contradicts what I wrote, but I fail to see the contradiction. Or am I misunderstanding the point of your post?
EDIT: Just saw that the quote includes using ALTER INDEX to rebuild a clustered index. Yes, that would work too - if there is an existing clustered index to rebuild. In this case, there was only a nonclustered index, so rebuilding was not an option.