Recently a client of mine asked me – When to Use DBCC CLEANTABLE? A very interesting question to be discussed in Comprehensive Database Performance Health Check.

DBCC CLEANTABLE reclaims space after a variable-length columns like varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and XML  is dropped. One should remember is that the same result can be achieved by just rebuilding the index as well. If you rebuild the index after dropping the variable-length column, you will get the same results. I personally prefer to rebuild the index overrunning this DBCC command. However, I must say that there is no visible drawback of this DBCC command.

DBCC CLEANTABLE (DBName,'SchemaName.TableName', 0) 

Here 0 stands for the current database.

Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Exit mobile version