Truncate vs Delete

• satishkmr538

  SSC Veteran

  Points: 258

  More actions

  January 27, 2015 at 11:01 pm

  #302659

  can truncate and delete rollback when we use transactions?

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  January 27, 2015 at 11:09 pm

  #1773569

  Yes, both can but do a simple test to prove it to yourself.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Bhushan Kulkarni

  SSCrazy

  Points: 2829

  More actions

  January 27, 2015 at 11:31 pm

  #1773574

  USE tempdb

  GO

  CREATE TABLE myTest (Id INT,Name VARCHAR(256))

  GO

  INSERT INTO myTest (Id,Name) VALUES (1,'abc')

  INSERT INTO myTest (Id,Name) VALUES (2,'def')

  INSERT INTO myTest (Id,Name) VALUES (3,'ert')

  INSERT INTO myTest (Id,Name) VALUES (4,'xyz')

  GO

  SELECT * FROM myTest

  BEGIN TRAN

  TRUNCATE TABLE myTest

  ROLLBACK TRAN

  SELECT * FROM myTest

  GO

  SELECT * FROM myTest

  BEGIN TRAN

  DELETE FROM myTest

  ROLLBACK TRAN

  SELECT * FROM myTest

  DROP TABLE myTest

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 28, 2015 at 7:21 am

  #1773676

  satishkmr538 (1/27/2015)

  ---

  can truncate and delete rollback when we use transactions?

  Sounds like an interview question to me. 😉

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/