:::: MENU ::::

Considerations for using Truncate table command

DBA best practices, dbDigger, Performance Tunning and Optimization

Considerations for using Truncate table command

While deleting bulk data from a table, TRUNCATE TABLE command is preferred than delete. Obvious reason is that it is unlogged and thus more efficient than logged operation delete.

The Syntax for this statement is:

TRUNCATE TABLE table_name

Table_Name: Is the name of the table to truncate or from which all rows are removed.

But while using this powerful alternate one should be aware of some more facts.

  • TRUNCATE TABLE is not fully unlogged but removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. While delete removes the data row by row.
  • Besides deleting all of the records in a table, this command will also reset the seed of any IDENTITY column back to its original value.
  • After you have run the TRUNCATE TABLE statement, it is important then to manually update the statistics on this table using UPDATE STATISTICS. This is because running TRUNCATE TABLE will not reset the statistics for the table, which means that as you add data to the table, the statistics for that table will be incorrect for a time period. Of course, if you wait long enough, and if you have Auto Update Statistics turned on for the database, then the statistics will eventually catch up with themselves. But this may not happen quickly, resulting in slowly performing queries because the Query Optimizer is using out-of-date statistics.
  • TRUNCATE TABLE will not work when there are Foreign Key references present for that table. A workaround is to DROP the constraints before firing the TRUNCATE.
  • As TRUNCATE TABLE is unlogged operation (at row level) so it can not be rolled back.
  • As TRUNCATE TABLE is not logged, it cannot activate a trigger.
  • Truncate is categorized as DDL command.
  • You can not truncate a table that is participant in an indexed view.
  • You can not truncate a table that is published using transactional replication or merge replication.
  • You can not truncate a table when there are Foreign Key references.
  • To truncate a table, you need at least ALTER permissions on the table (which is granted by default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles).
  • TRUNCATE TABLE can not be used with where clause. So no condition could be applied while truncating table. So it turns to a lethal weapon, use it with care!
Consult us to explore the Databases. Contact us