We know that SQL Server is smart to reclaim the space used by the deleted rows. But when a table is altered to drop one or more variable length columns, then the space used by those columns is not immediately reused by SQL Server.

I will illustrate the same with an example:

— Changing the database
USE Database_for_Blogs

— Creating the Test table
CREATE TABLE dbo.test
(
id INT,
col2 CHAR(25),
variable_col VARCHAR(5000)
)

–Inserting some dummy data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE @cnt < 3000
BEGIN
SELECT
@cnt = @cnt + 1 ;
INSERT
dbo.Test ( id,col2,variable_col)
VALUES (
@cnt
,‘row number ‘ + CAST(@cnt AS VARCHAR(10))
,REPLICATE(‘test’, 1250)
) ;
END

Lets check the values inserted in the Test table.

SELECT * FROM dbo.test

By using the following query we can see how much space Test table is using.

SELECT
avg_fragmentation_in_percent
,page_count
,avg_page_space_used_in_percent
,record_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID(‘Database_for_Blogs’),OBJECT_ID(‘test’),NULL,NULL,‘DETAILED’)

Now I will drop the variable_col from the Test table.

ALTER TABLE test
DROP COLUMN variable_col

Now lets again check the space used by the Test table:

SELECT
avg_fragmentation_in_percent
,page_count
,avg_page_space_used_in_percent
,record_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats ( DB_ID(‘Database_for_Blogs’),OBJECT_ID(‘test’),NULL,NULL,‘DETAILED’)

So from the above figure we can see that SQL Server using the same number of pages even after dropping the column that was consuming the most space.

This is because when a table is altered to drop a column, SQL Server does not remove the column data from the data pages. Instead it updates the metadata in the system tables so that when queried, it appears as if the column no longer exists. The data is still present in the data pages, but it’s not returned as a part of a result set. Thus, the space cannot be reused initially.

Cheers,

Subhro Saha

Advertisements