SQL Server 2005: Disk Space Used by Flushed Columns

Question

I have a large table in SQL Server 2005 that takes up about 3.5 GB of space (according to sp_spaceused). It contains 10 million records and several indexes.

I just deleted a bunch of columns from it, so the record length was reduced to half, and, to my surprise, it took zero time. Obviously, sp_spaceused was still reporting the same occupied space, the SQL server did nothing when deleting the columns, except that they were marked as “discarded”.

So I moved all the data from this table to another new table, truncated it and moved all the data back to get all reconstructed.

Now, after that, the data occupies 2.8 GB, which is less than before, but I expected a larger decline.

Is it possible that the fact that this table originally had these columns still leaves something there?

Truncated him not enough? Should I reset it and create it again with a smaller set of columns?

Or does the data really accept 2.8 GB?

Thanks!

Answer 1

You will need to rebuild the clustered index (if you have one - by default your primary key is the clustered key).

ALTER INDEX (your clustered index) ON TABLE (your table) REBUILD

- "", , .

, DBCC SHRINKDATABASE , . !

Answer 2

, " "? , 8K , , , , . ( ), 7.5K , . , - 5K, .