40

One thing struck in my head about mysql/sqlserver i.e delete/ truncate

Which one is better and faster ?

where to use delete?

where to use truncate?

4 Answers4

123

DELETE

  1. DELETE is a DML Command.
  2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
  3. We can specify filters in where clause
  4. It deletes specified data if where condition exists.
  5. Delete activates a trigger because the operation are logged individually.
  6. Slower than truncate because, it keeps logs.
  7. Rollback is possible.

TRUNCATE

  1. TRUNCATE is a DDL command.
  2. TRUNCATE TABLE always locks the table and page but not each row.
  3. Cannot use Where Condition.
  4. It Removes all the data.
  5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
  6. Faster in performance wise, because it doesn't keep any logs.
  7. Rollback is possible.


  • DELETE and TRUNCATE both can be rolled back when used with TRANSACTION (TRUNCATE can be rolled back in SQL Server, but not in MySQL).
  • if there is a PK with auto increment, truncate will reset the counter

http://beginner-sql-tutorial.com/sql-delete-statement.htm

Community
  • 1
  • 1
bgs
  • 3,061
  • 7
  • 40
  • 58
  • 1
    +1 for detailed explanation and clear formatting – zinking Dec 13 '13 at 06:15
  • 3
    Truncate will also change auto-increment value to 1. Delete will not keep existing auto-increment value – Onimusha Nov 20 '14 at 14:20
  • 1
    @Onimusha, This point already specified in last line "if there is a PK with auto increment, truncate will reset the counter" – bgs Nov 21 '14 at 05:59
  • In mysql, an implicit commit occurs with TRUNCATE therefore probably not suitable in transaction - even if Temporary table. Therefore most likely better off with DELETE inside a transaction when using mysql – Andrew Blake Sep 05 '15 at 01:14
  • 4
    TRUNCATE **cannot** be rolled back! http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html – Devy May 10 '16 at 17:31
  • @Devy, you are wrong.. Rollback working fine for truncate table.. Pls verify and confirm.. http://blog.sqlauthority.com/2010/03/04/sql-server-rollback-truncate-command-in-transaction/ – bgs May 18 '16 at 09:47
  • @bgs Calling me wrong with a random blog post doesn't make you more right. And you are talking different things. My link is explicitly pointing at the official MySQL documentation. And doc reads verbatim: "Truncate operations cause an implicit commit, and so cannot be rolled back." No sure what your link is referring to, perhaps SQL Server? – Devy May 18 '16 at 17:22
  • @Devy, i am not blind to post the random post..But am agree with "TRUNCATE cannot be rolled back" only in MYSQL but it can be rollback In sql server so only i post the reference link. – bgs May 20 '16 at 10:41
  • 2
    @bgs ok we are even then. The answer should probably be augmented with the context and emphasis the differences between MySQL and SQL Server in TRUNCATE. – Devy May 20 '16 at 16:32
  • 1
    In MySQL 5.5+, truncate may not work if the table is referenced by a foreign key constraint (regardless of whether the records in the table are actually referenced): at https://dev.mysql.com/doc/refman/5.5/en/truncate-table.html it says: "TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table." – Nicholas Daley-Okoye Sep 20 '19 at 10:16
13

Difference

The most important difference is DELETE operations are transaction-safe and logged, which means DELETE can be rolled back. TRUNCATE cannot be done inside a transaction and can’t be rolled back. Because TRUNCATE is not logged recovering a mistakenly TRUNCATEd table is a much bigger problem than recovering from a DELETE.

DELETE will fail if foreign key constraints are broken; TRUNCATE may not honor foreign key constraints (it does for InnoDB tables). DELETE will fire any ON DELETE triggers; TRUNCATE will not.

FASTER

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

Where to USE

truncate

when table set to empty, and need reset auto-incrementing keys to 1. It's faster than DELETE because it deletes all data. DELETE will scan the table to generate a count of rows that were affected.

delete

need rows to delete based on an optional WHERE clause. need logs and apply foreign key constraints

mguymon
  • 8,946
  • 2
  • 39
  • 61
naveen goyal
  • 4,571
  • 2
  • 16
  • 26
3

Difference:

  1. Truncate deletes the complete data from the table and next auto increment id will start with 1 whereas Delete will start with next id.
  2. Both will keep structure intact and delete data only.
  3. with Delete you can use limit whereas with Truncate you can't.
Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90
3

The DELETE command is used to remove rows from a table

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

Linga
  • 10,379
  • 10
  • 52
  • 104