What are the disadvantages of TRUNCATE in SQL Server?

TRUNCATE command consumes less time compared to DELETE. Also, Truncate operation will not consume space in transaction log.

Despite of these advantages, TRUNCATE will have its own disadvantages.

  • TRUNCATE will reset the seed of an identity column to its initial value. If we don't want seed of identity column to reset after data deletion from table, then truncate is not an apt choice.

  • Statistics on table will not be updated automatically after TRUNCATE operation unless AUTO UPDATE STATISTICS is ON . So, After every TRUNCATE Statement it's required to update the statistics on table manually.  If this operation is not done, then wrong statistics will up used for sometime, which may hit the performance.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 Optimiser is using out-of-date statistics.  

This entry was posted in . Bookmark the permalink.

Leave a reply