Delete

• 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!

• How to keep track of how many records were truncated?

  Oct 9, 2018

  Someone had an issue where a third party app would truncate a table for maintenance and they had no control over the truncate and when it happened. The issue was that they needed to know how many records were in the table before the truncate occurred.

  Let’s start by understanding what DELETE and TRUNCATE are and how they work.

  When you delete from a table, you are removing rows. You can specify what rows to delete with a WHERE clause. When you TRUNCATE a table, it is not logging what rows are deleted and it does perform minimal logging. TRUNCATEis a great way to clear a table without causing the Transaction Logs to balloon.