Reclaiming the table space after dropping a column - [With Clustered Index]

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a clustered Index.

Create a table with clustered index in it:

Create Table tblDemoTable (
[Sno] int primary key clustered,
[Remarks] char(5000) not null
)
Go

Pump-in some data into the newly created table:

Set nocount on
Declare @intRecNum int
Set @intRecNum = 1

While @intRecNum <= 15000
Begin
Insert tblDemoTable (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109))
Set @intRecNum = @intRecNum + 1
End

If it's SQL 2000 or earlier:

DBCC SHOWCONTIG ('dbo.tblDemoTable') -- Displays fragmentation information for the data and indexes of the specified table
Go

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable' table...
Table: 'tblDemoTable' (1717581157); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 80
- Extents Scanned..............................: 12
- Extent Switches..............................: 11
- Avg. Pages per Extent........................: 6.7
- Scan Density [Best Count:Actual Count].......: 83.33% [10:12]
- Logical Scan Fragmentation ..................: 3.75%
- Extent Scan Fragmentation ...................: 8.33%
- Avg. Bytes Free per Page.....................: 33.7
- Avg. Page Density (full).....................: 99.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you are using SQL 2005:

As that DBCC feature would be removed in the future version of SQL Server I would suggest the following code snippet instead of DBCC SHOWCONTIG.

Declare @object_id int;
Set @object_id = Object_ID(N'Testbed.dbo.tblDemoTable');

Begin
Select index_type_desc, index_depth, index_level,
avg_fragmentation_in_percent, avg_fragment_size_in_pages,
page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (Db_id(), @object_id, NULL, NULL , 'Detailed');
End
Go

Output:


Drop the column 'Remarks' from the table:

Alter table tblDemoTable drop column Remarks
Go

Now try out DBCC SHOWCONTIG or sys.dm_db_index_physical_stats as explained previously and verify that the details haven't changed a bit :)

Solution:

DBCC DBREINDEX ( 'dbo.tblDemoTable' )
Go

Now try out either the SHOWCONTIG or dm_db_index_physical_stats and see that you have reclaimed the space successfully.

DBCC SHOWCONTIG ('dbo.tblDemoTable')
Go

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable' table...
Table: 'tblDemoTable' (1717581157); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 25
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 5.0
- Scan Density [Best Count:Actual Count].......: 80.00% [4:5]
- Logical Scan Fragmentation ..................: 8.00%
- Extent Scan Fragmentation ...................: 20.00%
- Avg. Bytes Free per Page.....................: 296.0
- Avg. Page Density (full).....................: 96.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

May be this is one another good example of why we need to have clustered index on a table :)

Similarly if you have run this DMV sys.dm_db_index_physical_stats then the output would be this:



BTW, in SQL 2005 though DBCC DBREINDEX would work, its better to start practicing ALTER INDEX syntax.

In the next post we would see how to reclaim the space in a table which doesn't have clustered index in it.

Related Article: Reclaim Unused Table space. This article was written couple of years back and was tested with SQL 2000 at that time.