Post reply

The Difference between Truncate and Delete in SQL Server

  • November 22, 2007 at 10:29 am

    #753440

    Mark Fyffe (11/22/2007)

    Actually my mistake this does work on temp tables but it does not work on variable tables nor does the drop table command. i.e.

    declare @t1 table

    (

    c1 varchar(10)

    )

    insert into @t1 values('chess')

    select * from @t1

    truncate table @t1 -- this will give you an error but using the below commented delete statement

    --would work

    --delete @t1

    select * from @t1

    --Drop table statement below also would give you an error if run.

    --Drop table @t1

    Hi mark,

    This is strange behaviour, and its a good topic, that why this happens so, i'll search about it and then let you know, Meanwhile if u find any reasons for this then do share the solution with us.

    Thanks,
    Prashant

  • November 22, 2007 at 11:34 am

    #753453

    Hello.

    There was a bit of redundancy in this article. Therefore, please forgive me if I overlooked your mention of a very useful byproduct of using TRUNCATE.

    If your table has an auto-increment column, the TRUNCATE command will RESET the internal counter ... whereas DELETE will not.

    For example, if your table has an auto-increment column with seed=1, and there are 20 records in your table, the last record's AI value will be 20. If you perform a DELETE and then add a new record, the new record's AI value will be 21. Conversely, if you now perform a TRUNCATE and then add a new record, the new record's AI value will be 1.

    This is a very important and useful feature of the TRUNCATE command.

    Kind Regards and Happy Thanksgiving to those of us here in the States!

    Paulie D.

  • November 22, 2007 at 1:08 pm

    #753458

    Paul (11/22/2007)

    Hello.

    There was a bit of redundancy in this article. Therefore, please forgive me if I overlooked your mention of a very useful byproduct of using TRUNCATE.

    If your table has an auto-increment column, the TRUNCATE command will RESET the internal counter ... whereas DELETE will not.

    For example, if your table has an auto-increment column with seed=1, and there are 20 records in your table, the last record's AI value will be 20. If you perform a DELETE and then add a new record, the new record's AI value will be 21. Conversely, if you now perform a TRUNCATE and then add a new record, the new record's AI value will be 1.

    This is a very important and useful feature of the TRUNCATE command.

    Kind Regards and Happy Thanksgiving to those of us here in the States!

    Paulie D.

    Hi Paulie,

    I think the point about which you are talking about is mentioned there and it is explained properly using the example, please have a look once again, but yes the explanation that you have given is easy to understand and do not requires any example.:)

    Thanks

    Thanks,
    Prashant

  • November 22, 2007 at 7:59 pm

    #753498

    Hi, this is really a wonderful article.:)

    I got a new learning today and cleared my thoughts on truncate. Due to some problem with my mouse, accidently the rating given by me was awful rather it should be five stars.

    Keep posting these articles 🙂

    Take care

    Sameer Kapur

  • November 23, 2007 at 12:22 am

    #753536

    Many a doubts related to truncate and delete statements got clarified.

    Thanks for the article

  • November 23, 2007 at 12:45 am

    #753541

    It's really too good. Topic was simple but very informative. It changed some of my views abt truncate. One of my question is DDL statement also fires some DDL triggers...Does Truncate do this?

  • November 23, 2007 at 12:58 am

    #753543

    Rajnish Kumar (11/23/2007)

    It's really too good. Topic was simple but very informative. It changed some of my views abt truncate. One of my question is DDL statement also fires some DDL triggers...Does Truncate do this?

    Hi Rajnish,

    Truncate do not fire any trigger.

    and one more thing can u please name the DDL statements those fires the triggers i think in SQL2005 this concept that (DDL statements can fire triggers) is implemented, in SQL2000 is there any DDL statement Which fire triggers????

    Please share your knowledge with us...

    Thanks

    Thanks,
    Prashant

  • November 23, 2007 at 1:26 am

    #753547

    Hi Prasant,

    U r right. DDL Triggers are available in SQL server 2005.

    DDL triggers, fire in response to a variety of Data Definition Language (DDL) statements. These statements are primarily statements that start with CREATE, ALTER, and DROP. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. DDL triggers can be used for administrative tasks such as auditing and regulating database operations.

    Tanks

  • November 23, 2007 at 3:33 am

    #753583

    Really good article Prasanth, Please keep posting more like this as it refreshes the knowledge of Experts, gives good brushing to Intermedeats and very valuable resource for novices.

    Keep it up.

  • November 23, 2007 at 12:03 pm

    #753690

    Excellent article on a topic I thought I already knew. Thanks for going into the subject in depth.

    Elliott

  • November 23, 2007 at 3:03 pm

    #753703

    Nice work, thanks. Good to learn.

    -gol

  • November 25, 2007 at 12:55 am

    #753803

    great article Thanks

  • November 26, 2007 at 1:10 am

    #753879

    Good article, but it would also be good to explicitly tell the effect when cascade delete is turned on for a relation with another table. My guess would be that truncate doesn't cascade the delete to the dependent table.

    Mike

  • November 26, 2007 at 1:17 am

    #753882

    Mike Perkins (11/26/2007)

    Good article, but it would also be good to explicitly tell the effect when cascade delete is turned on for a relation with another table. My guess would be that truncate doesn't cascade the delete to the dependent table.

    Mike

    Hi Mike,

    Can u please explain me that "cascade delete" means when a table is refrenced or connected with other table usinga foriegn key, and when we try to delete data from any table the it means we are performing the cascade delete operation, i am having this doubt caz from last 2-3 days i am reading about triggers and i found this term cascade in many articles. Is cascade means forign key relation???

    Is it....

    Thanks,
    Prashant

  • November 26, 2007 at 7:09 am

    #754020

    I rated average because:

    1 - You have a type mismatch in your desription of your statements regarding the table name.

    2 - You use the words remove / delete inconsistenly when you explain the difference.

    ---

    Other that that, the last part about the locking and constraints is very good.

Viewing 15 posts - 16 through 30 (of 66 total)

You must be logged in to reply to this topic. Login to reply