Nipun Tomar
What is the difference between TRUNCATE and DELETE?
By Nipun Tomar in Databases & DBA on Dec 17 2013
  • Pankaj  Kumar Choudhary
    May, 2015 29

    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 not possible.

    • 1
  • Isham Khan
    Apr, 2015 30

    delete basically delete all rows but PK(id) will start from that location eg if we insert 10 records and then delete that then id will start from 11 while if we use truncate all rows will be deleted and id will reset(start from 1)

    • 0
  • Satyam Verma
    Apr, 2015 9

    truncate use removing all records from tables. you can not use where clause in case of truncate but with delete , you can delete particular record from the tables.

    • 0
  • Sampa Setty Saravanan
    May, 2014 23

    DELETE :1. This statement delete one or more rows based on condition 2. We can get back the rows using ROLLBACK statement TRUNCATE: 1. This Statement delete all the rows. 2. We cannot get back the deleted rows using ROLLBACK statement

    • 0
  • Munesh Sharma
    Apr, 2014 13

    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 not possible.For more difference http://dotnet-munesh.blogspot.in/2013/12/difference.html

    • 0
  • Mukesh Kumar Tiwari
    Apr, 2014 1

    DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

    • 0
  • Ashish Singh
    Dec, 2013 22

    truncate command delete all records from the table and using delete command delete one record or more records,

    • 0

Most Popular Job Functions


MOST LIKED QUESTIONS