SQL SERVER – TIPS – Reclaim space for a table after drop column.

In a table, after a column is dropped using alter table drop column statement, is necessary to reclaim the space, just when a fixed-length column or text column were dropped.

To see the behavior you can create 3 tables with 3 fields, this 3 fields must to be different datatypes

-- create a db test create database db_test go use db_test go -- create 3 table with the same fields create table test1 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) go create table test2 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) go create table test3 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) Go

Now, you can add some information and see the space of each table.

-- insert some information into the 3 tables. insert into test1 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') insert into test2 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') insert into test3 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') --checking the space sp_spaceused test1 Go sp_spaceused test2 Go sp_spaceused test3 GO /* --Result test1 1 32 KB 24 KB 8 KB 0 KB test2 1 32 KB 24 KB 8 KB 0 KB test3 1 32 KB 24 KB 8 KB 0 KB */

Then, drop a column for each table, after drop a column the space for each table is the same than before. To reclaim the space is necessary to execute the dbcc cleantable on each table. This dbcc utility must to be executed when a variable-length column or text column were dropped.

-- drop one column in each table in order to -- see the behavior after drop a column alter table test1 drop column a alter table test2 drop column b alter table test3 drop column c --reclaim space dbcc cleantable ('db_test','test1') dbcc cleantable ('db_test','test2') dbcc cleantable ('db_test','test3')

MSDN says “DBCC CLEANTABLE reclaims space after a variable-length column or a text column is dropped by using the ALTER TABLE DROP COLUMN statement. The command does not reclaim space after a fixed-length column is dropped.”

After execute DBCC CLEANTABLE, you can see that some space was reclaimed for the table test3 where the TEXT column was dropped, for the other two tables the space is the same, even for the table 2 where a variable-length column was dropped, in this case some space must to be reclaimed, perhaps does not happening anything because the amount of data is not relevant.

sp_spaceused test1 Go sp_spaceused test2 Go sp_spaceused test3 GO /* --Result test1 1 32 KB 24 KB 8 KB 0 KB test2 1 32 KB 24 KB 8 KB 0 KB test3 1 24 KB 16 KB 8 KB 0 KB */