5

When do we use the DELETE command versus the TRUNCATE command? I am trying to find on the Internet but both commands delete the data; I can't tell the difference.

reformed
  • 4,505
  • 11
  • 62
  • 88
Adnan Siddique
  • 75
  • 1
  • 4
  • Truncate command is faster than delete command. If you want to delete whole table then better use truncate command – Naved Munshi Nov 28 '14 at 08:00
  • 2
    you can refere to following: http://stackoverflow.com/questions/20559893/comparison-of-truncate-vs-delete-in-mysql-sqlserver – Amy Roy Nov 28 '14 at 08:02
  • http://stackoverflow.com/questions/2763295/what-is-the-diffrence-between-truncate-and-delete-in-sql-server/2763332#2763332 – Shantanu Gupta Nov 28 '14 at 11:35
  • Did you really ask what's the difference when you completely empty the table and when you delete certain records from it? It's **common sense** what to use and when. – N.B. Nov 29 '14 at 17:40

1 Answers1

13

DELETE FROM TABLE 

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.
8. LIMIT clause can also be used to set a limit on the number of rows to be deleted.
9. ORDER BY clause can be used in DELETE statement. In this case, the rows are deleted in the specified order.

TRUNCATE TABLE

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 reset the auto increment number.
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.
8. Cannot use LIMIT and ORDER BY.

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION. If there is a primary key with auto increment, truncate will reset the counter.

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83