You must Sign In to post a response.
  • Category: SQL Server

    What is the difference between truncate and delete

    What is the difference between truncate and delete
  • #743897
    DELETE is a DML Command where as TRUNCATE is a DDL command. DELETE statement is executed using a row lock, each row in the table is locked for deletion where as TRUNCATE TABLE always locks the table and page but not each row. We can specify filters in where clause for delete and it deletes specified data if where condition exists Cannot use Where Condition for truncate and It Removes all the data. Delete activates a trigger because the operation are logged individually TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. Slower than truncate because, it keeps logs. Rollback is possible in delete but not in TRUNCATE

  • #743898
    Hi Rajanikant

    Delete:

    The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

    Ex.

    SQL> DELETE FROM emp WHERE job = 'CLERK';

    4 rows deleted.

    SQL> DELETE FROM emp

    All row deleted.


    Truncate :

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

    Ex.

    SQL> TRUNCATE TABLE emp;

    Table truncated.


    One more thing is DROP table

    The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

    Ex.

    SQL> DROP TABLE emp;

    Table dropped.

    Regards
    --------------
    Sudeep Bhawsar

  • #743901
    hii,

    The main difference between delete and truncate is that
    Deleted data can be rollbacked while truncated data can't

    Hope this helps you

    Regards
    Shalini

  • #743919
    Hai Rajanikant,
    This is the very basic question and if you would search in our website, you would have got plenty of answers. So before posting anew question, you should search in the existing and if you are not satisfied with the answer then only you can write a new post. It is good for all of us to search the correct answer rather than getting the duplicate answers.
    Anyway,Truncate used to delete all the row at a time and you cant use any condition like where clause.
    Delete is used to delete row by row and you can apply the Where clause to filter and delete only the filtered rows.
    Deleted row by using truncate cant be retrieved but deleted row using Delete command can be retrieved by using the OnDelete trigger.
    Truncate is mainly used by DBA while delete is used by the developer.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #743921
    K thanks to all

  • #744041
    HI...

    Delete:

    Detele is used remove one or more or entire rows from a table with where clause. After delete we can able to retrive it with the help of rollback.

    Ex:

    delete from table1 where id =1 // For deleting one single row
    delete from table1 // For deleting the whole

    Truncate :

    It is also used for removes all rows from the table. but the only one difference is we won't get back that deleted rows from the table

    ex:

    Truncate table table 1

  • #744574
    Hi,
    difference between truncate and delete --

    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.

    DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.

    Regards

    Regards,
    Dipti Choudhari


  • Sign In to post your comments