alter table drop column and dbcc cleantable

Microsoft SQL Server


I just want to make sure my understanding is correct:

1) alter table drop column does not release space of varchar columns and
other columns of variable length.

2) dbcc cleantable frees this space

Open questions:

3) What about the space used by fixed size columns? Is it reclaimed
during drop (timing lets me suspect that it's not)? dbcc cleantable "does
not reclaim space after a fixed length column is dropped."

Are rows inserted after a DROP COLUMN smaller?

Thanks a lot!


No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)


Well, its actually two questions.

1. DBCC CLEANTABLE does not reclaim CHAR and NCHAR columns, nor does the
ALTER TABLE ... DROP COLUMN statement. Correct.

2. Are the rows inserted after the ALTER TABLE statement has been issued?
Yes, new rows will only occupy the storage required for the new data
definition contingent on the values of PAD_INDEX and FILLFACTOR for the
Clustered Index.

Now, the next logical question would be: how to I reclaim the space after
issue the ALTER TABLE ... DROP COLUMN statement with a fixed-length column?

Rebuild the Clustered Index.

Anthony Thomas



Ok, so basically the space is not reclaimed until either the complete
table is rebuild or old records are deleted.

Thanks a lot! You get the virtual hug: *hug*