alter table drop column and dbcc cleantable

All,
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."
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
Are rows inserted after a DROP COLUMN smaller?
Thanks a lot!
Kind regards
robert
Robert Klemme wrote:
> All,
> 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."
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert
|||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.
Sincerely,
Anthony Thomas

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
Robert Klemme wrote:
> All,
> 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."
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> Are rows inserted after a DROP COLUMN smaller?
> Thanks a lot!
> Kind regards
> robert
No one wants to answer this one? Come on... I can even offer a virtual
hug. :-)
Thanks!
robert
|||Anthony Thomas wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:%23ypiPd4ZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Robert Klemme wrote:
>
http://msdn.microsoft.com/library/de..._dbcc_4bah.asp
> 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.
Ok.

> 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.
Ok.

> 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.
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*
:-)
Kind regards
robert