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/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp

Are rows inserted after a DROP COLUMN smaller?

Thanks a lot!

Kind regards

    robert

Robert Klemme wrote:
Show quoteHide quotehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
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 wrote:
Show quoteHide quotehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
No one wants to answer this one?  Come on...  I can even offer a virtual
hug. :-)

Thanks!

    robert

Anthony Thomas wrote:
Show quoteHide quotehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4bah.asp
Show quoteHide quoteOk.

Ok.

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

Problem with osql.exe -L
Simple Question: What's the best way to duplicate a DB under another name?
Server: Msg 7391, Level 16, State 1, Procedure <OBJECT>, Line
varchar vs nvarchar
stored procedure with different criteria
SQL7 2 DB files down to one???
Query Cost - how is it calculated?
Removing Identity Property
Autonumber
Format Numbers