Reduced Row size in SQL hasn't reduced table size

Can someone explain some behaviour I'm seeing in SQL Server 2005?

I've been tasked with reducing the size or our DB.

The table contains nearly 6 million records, and I calculated the row size as being 199开发者_高级运维0 bytes. I took a copy of the table, and reduced the row size down to 803 bytes, through various techniques.

When I compare the original table's Data Size (right-click properties or sp_spaceused) with the new table I'm seeing saving of just 21.7 MB. This is nowhere near what I was expecting.

Here is how I calculated the row-size: If the column was numeric/decimal then I used the MSDN size (http://msdn.microsoft.com/en-us/library/ms187746.aspx), for everything else I used syscolumns.length. If the column was nullable I added an extra byte.

Here are some of the changes I implemented.

1. Turned unnecessary nvarchars into varchars
2. Made columns NOT NULL
3. Reduced max length of varchar columns to suit actual data
4. Removed some unused columns
5. Couple of datetime into smalldatetime
6. Turned some decimals into ints.
7. Merged 16 nullable BIT columns into a bit masked int.

From this, my calculations showed a 60% row size reduction and against a 6M row table I would have expected more than 21MB of saving. It went down from 2,762,536 KB to 2,740,816 KB.

Can someone please explain this behaviour to me?

p.s. This does not take into account any indexes.

The problem is that altering a table does not reclaim any space. Dropping a column is logical only, the column is hidden, not deleted. Modifying a column type will often result adding a new column and hiding the previous one. All these operations increase the physical size of the table. To reclaim the space 'for real' you need to rebuild the table. With SQL 2008 and up you would issue an ALTER TABLE ... REBUILD. In SQL 2005 you can issue DBCC REINDEX(table).

I think you need to rebuild the clustered index on the table.