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.