SQL Server DBCC Commands: DBCC CLEANTABLE

DBCC CLEANTABLE is a DBCC command in Microsoft SQL Server that can be used to remove deleted rows from a table and reclaim the space occupied by the deleted rows. It is similar to the TRUNCATE TABLE command, but unlike TRUNCATE TABLE, DBCC CLEANTABLE preserves the structure of the table and does not reset the identity values of any identity columns in the table.

To use DBCC CLEANTABLE, you must specify the name of the database and the name of the table you want to clean. You can also specify a partition number if you want to clean a specific partition of a partitioned table.

Here is an example of how to use DBCC CLEANTABLE to remove deleted rows from a table:

-- Remove deleted rows from the "Customers" table in the "MyDatabase" database
DBCC CLEANTABLE ('MyDatabase', 'Customers');

The output of DBCC CLEANTABLE will be a message indicating whether the operation was successful or not. If the operation was successful, the deleted rows will be removed from the table and the space occupied by the deleted rows will be reclaimed.

It is important to note that DBCC CLEANTABLE only removes deleted rows from a table. It does not remove rows that have been updated or inserted. To remove all rows from a table, you can use the TRUNCATE TABLE command.

DBCC CLEANTABLE can be a useful tool for reclaiming space in a table and improving the performance of queries that access the table, but it should be used with caution.