DML

• Truncate vs Delete

  Jan 29, 2019

  There are many myths and misconceptions regarding TRUNCATE in SQL Server. I think this is heavily influenced by how other Relational Database Management Systems (RDBMS) handle TRUNCATE.

  Myth one: You can’t rollback TRUNCATE.

  You can actually ROLLBACK a TRUNCATE statement! (If it’s SQL Server.) Go ahead and create a table in a dev environment and give it a try!

  CREATE TABLE dbo.TruncateVSDelete_RandomstringofStuff 
  (ID INT);
  INSERT INTO dbo.TruncateVSDelete_RandomstringofStuff
  VALUES (1);
  BEGIN TRANSACTION
  SELECT * FROM dbo.TruncateVSDelete_RandomstringofStuff;
  TRUNCATE TABLE dbo.TruncateVSDelete_RandomstringofStuff;
  SELECT * FROM  dbo.TruncateVSDelete_RandomstringofStuff;
  ROLLBACK TRANSACTION
  SELECT * FROM dbo.TruncateVSDelete_RandomstringofStuff;
  DROP TABLE dbo.TruncateVSDelete_RandomstringofStuff;
  

  In addition to being able to ROLLBACK a TRUNCATE statement, it doesn’t matter whether your database is in FULL , SIMPLE , or BULK-LOGGED , you can ROLLBACK regardless!