删除变长列字段后使用DBCC CLEANTABLE回收空间

SQL Server在删除变长列或者减小变长列的长度后，表的大小不会响应自动减小，除非DBA重建索引或者reorganized索引。变长列包括varchar,nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext,image, sql_variant,和xml。

SQL Server提供了一个DBCCCLEANTABLE的命令可以回收表或索引视图中已删除的可变长度列的空间。

下面我们做个测试：

1. --创建测试表

CREATE TABLE testfreespace

( column1 INT

,column2 CHAR(20)

,column3 VARCHAR(8000))

2. --插入数据

DECLARE @count INT;

SET @count = 0;

WHILE @count < 3000

BEGIN

SELECT

@count = @count + 1;

INSERT into testfreespaceVALUES( @count,'test row # '+CAST(@countASVARCHAR(10)),REPLICATE('TestData', 3000)) ;

END

3. --查看使用空间使用情况

SELECT

alloc_unit_type_desc,

page_count,

avg_page_space_used_in_percent,

record_count

FROM sys.dm_db_index_physical_stats(DB_ID('FNDBLogtest'),OBJECT_ID(N'Testfreespace'),NULL,NULL,'Detailed')

结果：

alloc_unit_type_desc page_countavg_page_space_used_in_percent record_count

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

IN_ROW_DATA 3000 99.27106498640973000

(1 row(s) affected)

4. --删除变长列Column3

ALTER TABLETestfreespaceDROPCOLUMNColumn3

5.使用3脚本查询看到avg_page_space_used_in_percent仍然是99.2710649864097 没有改变。

6. --执行DBCC CLEANTABLE

DBCC CLEANTABLE(FNDBLogTest,"Testfreespace")

7.再次运行3脚本看到下面的结果：

alloc_unit_type_desc page_countavg_page_space_used_in_percentrecord_count

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

IN_ROW_DATA 3000 0.382999752903385 3000

(1 row(s) affected)

可以看到现在avg_page_space_used_in_percent已经变了，证明空间已经释放。

更多信息参考：DBCCCLEANTABLE (Transact-SQL)http://msdn.microsoft.com/zh-cn/library/ms174418.aspx