Sql-server – How to reduce size of SQL Server table that grew from a datatype change

database-tablesql-servertypes

I have a table on SQL Server 2005 that was about 4gb in size.

(about 17 million records)

I changed one of the fields from datatype char(30) to char(60) (there are in total 25 fields most of which are char(10) so the amount of char space adds up to about 300)

This caused the table to double in size (over 9gb)

I then changed the char(60) to varchar(60) and then ran a function to cut extra whitespace out of the data (so as to reduce the average length of the data in the field to about 15)

This did not reduce the table size. Shrinking the database did not help either.

Short of actually recreating the table structure and copying the data over (that's 17 million records!) is there a less drastic way of getting the size back down again?

Best Solution

You have not cleaned or compacted any data, even with a "shrink database".

DBCC CLEANTABLE

Reclaims space from dropped variable-length columns in tables or indexed views.

However, a simple index rebuild if there is a clustered index should also do it

ALTER INDEX ALL ON dbo.Mytable REBUILD

A worked example from Tony Rogerson