Use DBCC CLEANTABLE to reclaim space after you delete a variable-length column field

Tags: sql Server Reclaim Space Contraction Table original works, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated. http://lzf328.blog.51cto.com/1196996/960310

When SQL Server deletes a variable-length column or decreases the length of a variable-length column, the size of the table does not respond automatically, unless the DBA rebuilds the index or reorganized the index. The variable-length columns include varchar,nvarchar, varchar (max), nvarchar (max), varbinary, varbinary (max), text, Ntext,image, sql_variant, and XML.

SQL Server provides a dbcccleantable command to reclaim the space of a variable-length column that has been deleted in a table or indexed view.

Let's do a test here:

1. -- Create a test table

CREATE TABLE testfreespace

(Column1 INT

, Column2 CHAR(+)

, Column3 VARCHAR(8000))

2. -- Insert Data

DECLARE @count INT;

SET @count = 0;

While @count <

BEGIN

SELECT

@count = @count + 1;

INSERT into testfreespaceVALUES(@count,' Test row # '+CAST(@countasVARCHAR (Ten)),REPLICATE(' TestData ', +)) ;

END

3. -- See usage of space usage

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 ')

Results:

Alloc_unit_type_desc Page_count avg_page_space_used_in_percent Record_count

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

In_row_data 99.2710649864097 3000

(1 row (s) affected)

4. --- Delete the variable length column Column3

ALTER tabletestfreespaceDROPCOLUMNColumn3

5. Using the 3 script query to see that avg_page_space_used_in_percent is still 99.2710649864097 unchanged.

6. -- execute DBCC cleantable

DBCC cleantable(fndblogtest, "Testfreespace")

7. Run the 3 script again to see the following results:

Alloc_unit_type_desc Page_count Avg_page_space_used_in_percentrecord_count

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

In_row_data 0.382999752903385

(1 row (s) affected)

You can see now that the avg_page_space_used_in_percent has changed and that the space has been released.

For more information, refer to:dbcccleantable (Transact-SQL)http://msdn.microsoft.com/zh-cn/library/ms174418.aspx

This article is from the "Focus on SQL Server Technology" blog, so be sure to keep this source http://lzf328.blog.51cto.com/1196996/960310

Come from:http://lzf328.blog.51cto.com/1196996/960310

Use DBCC CLEANTABLE to reclaim space after you delete a variable-length column field