Reclaim space in SQL 2005 table after changing datatype?

Question

I inhereited a database with tables with many nvarchar columns. The tables were getting fairly large, and I decided to change the datatypes to varchar to cut storage because we do not use international characters. The "data space" on the table (Right-click, then "Properties") has not changed. However, if I copy this table into a new table, the data space used there is about 60% the size of the original table.

I have used 'DBCC CLEANTABLE' in other instances to reclaim space after DROPPING a variable-length column. But this doesn't seem to work when CHANGING variable length data types. How do I go about getting this space back?

For those not familiar with DBCC CLEANTABLE, MS has a good article on it with sample code against AdventureWorks.

http://msdn.microsoft.com/en-us/library/ms174418(SQL.90).aspx

Answer 1

Rebuild the clustered index if the DBCC does not work.

Answer 2

As BOL specifies CLEANTABLE only free's up pages from variable length columns. Your databse is probably fragmented, which occurs normally during database operation. It looks like SQL Server considers a fragmented page as being in use and does not display it as space available (btw sp_spaceused calls this unallocated space)

You can see how fragmented your tables are with DBCC SHOWCONTIG.

By re-building all the indexes in your database, you will get rid of fragmentation, which is pretty much what happens when you copy this data somewhere else.