SQL Server Table keeps big after schema change

Question

I have a table in SQL Server 2012 with 460.000 rows.

This Table has the following Schema:

We had an issue with our application so all 500.000 rows were created with Comments filled up with spaces.

This led to a table-size of approximately 3.7GB:

After this I did the following to fix the ending spaces, but the database stays at 3.7GB.

update voucher set comments = ltrim(rtrim(comments))

A quick calculation showed me, that one completely filled row has approximately 280 bytes of raw data and one empty row (all varchars empty) has 32 Bytes.

This means my table should be between 32MB-122MB for 460.000 rows.

Why is the table 3.7GB? How can I reclaim the space?

Answer 1

Your rows fit in page, even with 100 spaces in Comments. Updating the Comments to ltrim(rtrim)) will make absolutely no difference in space used, since the rows will be left in place in their respective pages (ie. absolutely no page gets freed, you just get more space available on each page). To reclaim the lost space you should rebuild the table:

ALTER TABLE ... REBUILD;

Not that 3.7Gb is big by any stretch...