1

What is the main difference between

truncate table mytable

and

delete from mytable
Pang
  • 9,564
  • 146
  • 81
  • 122
Jozsef Naghi
  • 1,085
  • 3
  • 14
  • 32
  • My google-fu reveals this: http://codebetter.com/raymondlewallen/2005/05/09/the-difference-in-truncate-and-delete-in-sql-server/ – Sean Oct 21 '14 at 08:02
  • 2
    possible duplicate of [What's the difference between TRUNCATE and DELETE in SQL](http://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql) – bfncs Oct 21 '14 at 10:46
  • Depends a bit of the DBMS, for example in MS SQL truncates also resets identity and cannot be performed if the table is targeted by a FK – jean Jul 02 '18 at 12:34

2 Answers2

1

TRUNCATE is executed using a table lock and whole table is locked for remove all records. DELETE is executed using a row lock, each row in the table is locked for deletion. TRUNCATE removes all rows from a table mean to say we cannot use where condition for truncate The DELETE command is used to remove rows from a table based on WHERE condition.

ascii
  • 64
  • 6
1

Though this question has been already answered, thought to compare TRUNCATE & DELETE side by side, with some basic points.

+----------------------------------------+----------------------------------------------+
|                Truncate                |                    Delete                    |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language)      | Its DML(Data Manipulation Language)          |
| command.                               | command.                                     |
+----------------------------------------+----------------------------------------------+
| Generally used to remove all the       | Generally used to delete particular rows of  |
| data from the TABLE.                   | data from the TABLE based on WHERE condition |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing     | We can Rollback after delete.                |
| Truncate.                              |                                              |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table.      | Delete does not reset identity of table.     |
+----------------------------------------+----------------------------------------------+
| It locks the entire table.             | It locks the table row.                      |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it.     | We can use WHERE to filter data to delete.   |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate.   | Trigger is fired.                            |
+----------------------------------------+----------------------------------------------+
| Faster in performance wise, because it |Slower than Truncate because it Keeps logs    |
| doesn't keep any logs.                 |                                              |
+----------------------------------------+----------------------------------------------+
| Syntax :                               | Syntax :                                     |
| 1) TRUNCATE TABLE Table_Name           | 1) DELETE FROM Table_Name                    |
|                                        | 2) DELETE FROM Table_Name WHERE <Condition>  |
+----------------------------------------+----------------------------------------------+
Linga Swamy Parandha
  • 553
  • 1
  • 5
  • 14