删除变长列字段后使用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_count avg_page_space_used_in_percent record_count
-------------------------------------------- ------------------------------ --------------------
IN_ROW_DATA 3000 99.2710649864097 3000
(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_count avg_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
