Changing char(10) to char(3) grew table even after cleantable and shrinkfile

Question

In a largely experimental attempt to reduce table space usage I altered a char(10) column to char(3) (The column's contents are never more than three characters long)

Steps (Not carried out in a single batch)

use thedatabase;
alter table thetable
alter column thecolumn char(3) null

DBCC CLEANTABLE (thedatabase,'thetable', 500000); 
/* Apparently not needed for fixed length column but just in case*/

DBCC SHRINKFILE (1);

The operation completed successfully, but the table actually grew in size. I know this can happen due to the way the sql engine modifies a column (creates a copy alongside and then copies the column contents) so I ran dbcc cleantable on the table, and then dbcc shrinkfile on the database file. None of these operations made any difference to the table size.

The table has no index, so not possible to run a rebuild index operation.

So my question is, why did the table grow, even after commands to shrink it.

Answer 1

This has most likely to do with forwarded records, the new char(3) value is copied to a new allocation unit (page) leaving a forwarding pointer in its place. You can check if they exist like this.

SELECT
    OBJECT_NAME(ps.object_id) as TableName,
    i.name as IndexName,
    ps.index_type_desc,
    ps.page_count,
    ps.avg_fragmentation_in_percent,
    ps.forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
INNER JOIN sys.indexes AS i
    ON ps.OBJECT_ID = i.OBJECT_ID  
    AND ps.index_id = i.index_id
-- WHERE forwarded_record_count > 0
   WHERE ps.object_id = object_id(<TABLENAME>)

The only way to fix this since you are on SQL Server 2005 would be to create a clustered index on the table and then to drop it. SQL 2008 would allows you to do ALTER TABLE <TABLE> REBUILD.

DBCC CLEANTABLE only cleans up variable length columns typed as varchar and nvarchar, see: DBCC CleanTable by Steve Stedman.