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)
1 Comment. Leave new
The 0 in DBCC CLEANTABLE (DBName,’SchemaName.TableName’, 0) is actually the batch size for the transaction.
When it is set to 0 then it will do the cleanup in one transaction which could fill up the transaction log file if it is a large table.
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-cleantable-transact-sql?view=sql-server-ver15