0

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?

0
4

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.