SQL Server Storage – Why Changing Column from DATETIME to DATE Shows No Storage Improvement?

alter-tabledisk-spacesql serversql-server-2008-r2

Long story short. I have a table with more than 10millions rows with a datetime field. WE can change that to DATE ( because date uses 3bytes, and datetime 8).

I thought if I:

alter table table1 alter column column1 date

I would reclaim more than half the space allocated.

but doing some tests, I can't see any space being reclaimed. 

exec sp_spaceused datas2
datas   110000                  8136 KB 8080 KB 8 KB    48 KB --with datetime
datas   110000                  8136 KB 8080 KB 8 KB    48 KB --with date

both with 8080KB.

but then if I create a new table, with the same ammount of data, but with DATE since the beggining, I can see a good storage improvement:

datas2  110000                  4744 KB 4688 KB 8 KB    48 KB

Now my table has 4688 KB.

How can I reclaim that space on a table that would be altered from datetime to date?

Best Answer

In several cases, you will need to rebuild an index and/or the clustered index in order to observe any freeing up of space.

If this is a heap, you can just say:

ALTER TABLE dbo.MyHeap REBUILD;
Related Question