List 10 difference between TRUNCATE & DELETE statement in SQL Server?

Leave a comment (5) Go to comments


This is a regular interview questions, which is being asked by interviewer for DEV DBA profiles. Here is a extensive list of difference between TUNCATE and DELETE Table statements.

TRUNCATE V/s DELETE

Truncate Delete
TRUNCATE is a DDL command DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row DELETE statement is executed using a row lock,                                             each row in the table is locked for deletion
Cannot use Where Condition We can specify filters in where clause
It Removes all the data It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. Delete activates a trigger because the operation                                are logged individually.
Faster in performance wise, because it is minimally logged in transaction log. Slower than truncate because, it maintain logs for every record
 Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table keeps object’s statistics and all allocated space. After a                       DELETE statement is executed,the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo The DELETE statement removes rows one at a time                       and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column DELETE retain the identity
Restrictions on using Truncate Statement
1. Are referenced by a FOREIGN KEY constraint.
2. Participate in an indexed view.
3. Are published by using transactional replication or merge replication.
Delete works at row level, thus row level constrains apply


More Details



Tags : TRUNCATE and DELETE,TRUNCATE Vs DELETE,DELETE vs TRUNCATE,DELETE vs TRUNCATE sql server, sql server TRUNCATE Vs DELETE,difference between TRUNCATE and DELETE ,how delete works in sql server, how truncate statement works in sql server

EOF - List 10 difference between TRUNCATE & DELETE statement in SQL Server?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

5 Comments.

  1. Delete
    1.DML data can be recovered by rollback before commit
    2.Remove any subset of data
    3.Delete is slower when table have numerous indexes and triggers
    4.DML triggers fire for delete mthod
    5.data can be recovered after commit by flashback method
    6.high water mark will remain the same
    7.data deleted info capture with returning clause
    8. you can not delete data if function based index is invalid
    9. can not delete data from complex views.
    10. Space is not freed

    Truncate
    1.Truncated Data can not be recoverd but truncated data is also logged
    2.truncate all or partition or subpartition data.
    3. truncate is faster as no logs we have to maintain ,no trigger firing
    4. reset the high water mark , space is released except reuse method
    5. can not truncate table indivisually if it is part of cluster
    6. can not truncate table with referential integerity constraint enable
    7. if table is not empty unuseable indexes become useable
    8. if domain index on table is invalid or in_progess state then we can not truncate the table
    8.we can not truncate a simple view , we can truncate MV but not simple view.

    For More Details on all this point with examples see my blog.

    more details at my blog :

    http://arunkaushikoracle.blogspot.in/2012/10/diff-bw-delete-truncate-in-oracle-db-on.html

  2. Rollback is possible for truncate. Please check

  3. Hi,
    The sentence under DELETE section are cutoff at the left side and not readable. Pls check the alignment. Thanks in advance.

    Regards
    Gopi

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.