SQL SERVER – When to Use DBCC CLEANTABLE?

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.

• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

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