27 August 2007 08:54 tonyrogerson

ALTER TABLE DROP COLUMN does not reclaim the space the column took - it's a meta data change only

When using ALTER TABLE <x> DROP COLUMN <z> SQL Server does not actually remove the data, it simply changes the Meta data in the system tables so that the column no longer exists. Let us take a look at this behaviour and how to reclaim your space. This entry came about because MarkC found you could add Nullable columns to a table that resides on a read only file group.

First create the table and sample data:

CREATE TABLE mytable (

    id  int not null identity primary key clustered,

    somedata    char(8000) not null

)

go

 

SET NOCOUNT ON

 

DECLARE @i int

SET @i = 1

 

WHILE @i <= 50000

BEGIN

    INSERT mytable ( somedata ) VALUES( '1234' )

 

    SET @i = @i + 1

 

END

Capture the current space used and page density stats:

DBCC SHOWCONTIG( mytable )

TABLE level scan performed.

- Pages Scanned................................: 50000

- Extents Scanned..............................: 6275

- Extent Switches..............................: 6274

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.60% [6250:6275]

- Logical Scan Fragmentation ..................: 0.37%

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 83.0

- Avg. Page Density (full).....................: 98.97%

sp_spaceused mytable, true

name     rows   reserved    data        index_size   unused

-------- ------ ----------- ----------- ------------ --------

mytable  50000  401544 KB   400000 KB   1496 KB      48 KB

You can see that the table is 401Mbytes, the average page density is 98.97% full, the average free bytes per page is just 83.

Now drop the column:

ALTER TABLE mytable DROP COLUMN somedata

DBCC SHOWCONTIG( mytable )

TABLE level scan performed.

- Pages Scanned................................: 50000

- Extents Scanned..............................: 6275

- Extent Switches..............................: 6274

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.60% [6250:6275]

- Logical Scan Fragmentation ..................: 0.37%

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 83.0

- Avg. Page Density (full).....................: 98.97%

sp_spaceused mytable, true

name     rows   reserved    data        index_size   unused

-------- ------ ----------- ----------- ------------ --------

mytable  50000  401544 KB   400000 KB   1496 KB      48 KB

As you can see there is no change in the physical space used statistics, even the bytes free per page and page density are the same which is worrying.

First thought I suppose would be to defrag the index:

DBCC INDEXDEFRAG( 0, mytable, 1 )

Pages Scanned        Pages Moved          Pages Removed

-------------------- -------------------- --------------------

50000                0                    0

No change; that did not reclaim any space which is understandable because the INDEXDEFRAG works at getting pages in the correct order and doesn’t touch rows.

The way I’d recommend is to use DBREINDEX.

DBCC DBREINDEX( mytable )

DBCC SHOWCONTIG( mytable )

 

TABLE level scan performed.

- Pages Scanned................................: 81

- Extents Scanned..............................: 11

- Extent Switches..............................: 10

- Avg. Pages per Extent........................: 7.4

- Scan Density [Best Count:Actual Count].......: 100.00% [11:11]

- Logical Scan Fragmentation ..................: 1.23%

- Extent Scan Fragmentation ...................: 9.09%

- Avg. Bytes Free per Page.....................: 71.3

- Avg. Page Density (full).....................: 99.12%

At last, we have normality – the column really has gone.

This relies on you using a clustered index, if your table has no clustered index then it’s called a heap and the space will not be reclaimed. If you have a table heap and drop a column on that table then you will need to copy the data out, truncate the table and copy it back in again, that may be drop and recreate the table, SELECT INTO etc..

If I had the time I’d take a look and see what effect this had on page splits, I’m almost wondering that the ‘free space’ that isn’t really ‘free space’ will not be used.

To summarise, always use a clustered index in your design, if you are going to drop a column from the table always use DBCC DBREINDEX afterwards to ‘really’ apply the drop and just to Meta data.

Filed under:

Comments

# Read Only File Group - Tables are not really Read Only in the strict sense.

27 August 2007 11:09 by Tony Rogerson's ramblings on SQL Server

Are read only file groups really read only? What behaviour do we expect on tables that reside on the

# Interesting Finds: August 27, 2007

27 August 2007 15:02 by Jason Haley

# re: ALTER TABLE DROP COLUMN does not reclaim the space the column took - it's a meta data change only

13 November 2007 19:32 by PaulIbison

Hi Tony - I was thinking it might be worth adding a comment about "DBCC CLEANTABLE" as a 'fixing statement'.

I've never personally used this DBCC and it wouldn't help in your particular example, but apparently it would if the column is varchar rather than char.

Cheers.

Paul Ibison

# How Can I Truly DELETE Data From A SQL Server Table &#8211; Still Shows Up In Notepad In Backup File? | Click &amp; Find Answer !

Pingback from  How Can I Truly DELETE Data From A SQL Server Table &#8211; Still Shows Up In Notepad In Backup File? | Click &amp; Find Answer !