stevecable (7/31/2009)
The answer claims "Even though TRUNCATE TABLE does remove all the data pages and could theoretically be a great time to change the metadata, it doesn't work this way (as can easily be verified by testing). "So, I tested truncation, and it sure seems to me like all the space is reclaimed. I get zero's across the board on data and indexes. I have to admit being baffled why truncation is not a correct option for reclaiming space. Hmmm.
Hi Steve,
Yes, truncating a table will free up all space allocated to it. No problem. But that is not the scenario this question was about. You have to relaod the rows that were there (without the now unused Column2). If you do that and check sp_spaceused, you'll see that the 200 bytes per row previously required for Column2 are still used for each row - in other words, after truncating and reloading the data, you still have not reclaimed any space. Run the sample below (make sure to create and populate MyTable2 first) to check:
DROP TABLE MyTable;
go
CREATE TABLE MyTable
(Column1 int NOT NULL,
Column2 char(200) NOT NULL,
Column3 varchar(40) NOT NULL,
PRIMARY KEY NONCLUSTERED(Column1));
INSERT INTO MyTable
SELECT * FROM MyTable2
go
EXEC sp_spaceused MyTable;
ALTER TABLE MyTable
DROP COLUMN Column2;
EXEC sp_spaceused MyTable;
TRUNCATE TABLE MyTable;
EXEC sp_spaceused MyTable;
INSERT INTO MyTable(Column1, Column3)
SELECT Column1, Column3
FROM MyTable2;
EXEC sp_spaceused MyTable;
CREATE CLUSTERED INDEX xx ON MyTable(Column1)
DROP INDEX MyTable.xx
EXEC sp_spaceused MyTable;
go
You'll see that after the TRUNCATE TABLE, the space used is zero - but after the INSERT INTO, it is right back at its original size, even though Column2 has already been dropped. Only after creating and dropping a clustered index (or creating, copying, and renaming a new table) will the data size of the table be reduced.