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

    Difference b/w drop,delete & truncate table?

    Can any1 tel m d difference b/w drop,delete & truncate table
  • #296906
    [Response removed by Admin. Read forum policies.]

  • #296909
    Hi


    Drop is delete the table and strecture too..

    TRUNCATE TABLE is functionally identical to DELETE
    statement with no WHERE clause: both remove all rows in the
    table. But TRUNCATE TABLE is faster and uses fewer system
    and transaction log resources than DELETE.

    The DELETE statement removes rows one at a time and records
    an entry in the transaction log for each deleted row.
    TRUNCATE TABLE removes the data by deallocating the data
    pages used to store the table's data, and only the page
    deallocations are recorded in the transaction log.

    TRUNCATE TABLE removes all rows from a table, but the table
    structure and its columns, constraints, indexes and so on
    remain. The counter used by an identity for new rows is
    reset to the seed for the column. If you want to retain the
    identity counter, use DELETE instead. If you want to remove
    table definition and its data, use the DROP TABLE statement.

    You cannot use TRUNCATE TABLE on a table referenced by a
    FOREIGN KEY constraint; instead, use DELETE statement
    without a WHERE clause. Because TRUNCATE TABLE is not
    logged, it cannot activate a trigger.

    TRUNCATE TABLE may not be used on tables participating in
    an indexed view.


    reagrds
    praveen

  • #296910
    Drop is used to delete the tables, stored procedure, triggers ect.

    Delete is used to delete the all or some rows in a table with where condition Delete is Data Manuplation Language command(DML).

    Truncate deletes the all the rows at one go you cannot use where condition in it and it is Data Definition Language command(DDL).

    HTH

    Regards,
    Satish.

    Regards,
    Satish

  • #296938
    main difference
    drop
    ..Delete the structure
    ..can't rollback
    delete
    ...DML statement
    ...can be rollback
    ...does not delete the identity of table

    truncate
    ddl statement
    ...can't be rollback

    ...delete the identity of table

  • #296963
    hi mahima

    Difference b/w Truncate and Delete
    -----------------------------------------------
    1) Truncate is Data Dafanition Language.
    Delete is Data Manipulation Language.

    2) Truncate command cannot be rollback
    Since DELETE IS DML, Delete command can be rollback.

    3) Truncate is faster than Delete.
    Delete is slower than Truncate.

    4) WHERE clause can be used with DELETE
    and not with TRUNCATE.

    5) When Table is Truncated memory occupied is released and also water mark is adjusted.
    When Table is deleted memory occupied is not released and also water mark is not adjusted


    DROP Will remove a table from DB

  • #298166
    The TRUNCATE TABLE statement is a fast, non-logged method of deleting all rows in a table.
    DELETE logs each row deletion, and TRUNCATE TABLE logs only the de-allocation of whole data pages.
    Truncate table does not cause DELETE triggers to fire.
    Truncate table will not work if the table is referenced in a foreign key constraint irrespective of the dependent table containing the keys or not.
    Delete statement will raise error, if the table is referenced in a foreign key constraint and there are depending rows in the child table on the keys.
    Truncate table resets the identity value to 1, delete does not reset the identity value.

  • #298286
    Hi-

    Drop Table Statement is Used to Delete the Table from DB.

    Delete Statement : Is Used to Delete the Record from a particular Table in this a log is maintained. means we can recover the data.

    Truncate Table: No Log is Maintained and the structure of the table is dropped in this

    Thanks
    Lakhan Pal Garg

    Please rate this answer if it helped you.
    Thanks & Regards
    Lakhan Pal Garg

  • #299128
    drop :is used to drop the entire table from the database

    syntax:drop table <table name>

    delete:delete is used to delete the record from table
    it can be used with where clause.it is slow when compared to truncate command.identity option is not resetted in case of delete command

    syntax:1.delete from <table name> --delete all the rows
    2. delete from <table name> where <condition>

    truncate:used to delete all the records from the table.it cannot be used with where clause.identity option is resetted in case of truncate command

    syntax:truncate table <table name> --deletes all the rows


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.