Sometime back I came across a question in one of the forums. The problem description is as follows.
- The user had a huge table
- Due to some reasons he decided to drop a couple of columns
- Even after the dropping the columns, the space used by the table remained the same as earlier.
Whenever I come across a situation like this, the first question that comes to my mind is “Are those variable length columns?“. Variable length columns are the ones which have the data type as
varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Whenever a column having these data types is dropped, the space utilized by this column is not automatically reclaimed. Here is an example. I create a table which has a column with
varchar data type.
CREATE TABLE [dbo].[TestTable]( [ID] [int] NULL, [Name] [varchar](100) NULL, [Address] [varchar](1000) NULL ) ON [PRIMARY]
After inserting few rows into the table, I drop the column Address. Before and after dropping the table, I check the Average Page Space used in Percentage value.
SELECT page_count,avg_page_space_used_in_percent,record_count FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed') go ALTER TABLE [TestTable] DROP COLUMN [Address] go SELECT page_count,avg_page_space_used_in_percent,record_count FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed')
After executing the query, the output looks like the one below.
In this situation DBCC CLEANTABLE command comes to the rescue. Executing this command after dropping a variable length column will release the space used by that column. Hence I include the following line after the DROP TABLE command.
DBCC CLEANTABLE ('dbname', 'TestTable', 0)
After executing the same query with this extra line, the output looks like this.