Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between Truncate and Delete


Difference between Truncate and Delete

Author
Message
Prashant Pandey
Prashant Pandey
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 128
Mark Fyffe (11/22/2007)
Excellent explanations especially the logging behaviour of both commands. I was hoping I would have saw some details on the behaviour of these commands on temporary tables. Feel free to let me/us know (via a forum post) if you enhance your article with this info as well.

Cheers,


Hi Mark,
Its very good for me and for all readers if u'll share your knowledge with us.
Please tell me about this,???
Thank you.

Thanks,
Prashant
Jai Kumar
Jai Kumar
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 Visits: 41
Thanks for the clarification.

We are expecting more articles like this from you.
mollari_uk
mollari_uk
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 4
Why do these two behave differently? I don't know, but T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don't want to reset you counter, then delete is there for you.


Just thought I'd chip in with my explaination for this: With delete you aren't always removing all of the rows from the table so you wouldn't want it to reset the counter. Truncate does it the other way because you are always deleting all of the rows (and it gives you the option in contrast to delete).
Mark F-428640
Mark F-428640
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 557
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
Prashant Pandey
Prashant Pandey
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 128
mollari_uk (11/22/2007)
Why do these two behave differently? I don't know, but T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don't want to reset you counter, then delete is there for you.


Just thought I'd chip in with my explaination for this: With delete you aren't always removing all of the rows from the table so you wouldn't want it to reset the counter. Truncate does it the other way because you are always deleting all of the rows (and it gives you the option in contrast to delete).



Hey mollari, You are right, delete do not resets the counter caz we can also perform the conditional based deletion with delete, But with Truncate we can't.
Thank you,Smile

Thanks,
Prashant
Prashant Pandey
Prashant Pandey
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 128
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
Paul-490154
Paul-490154
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 17
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.
Prashant Pandey
Prashant Pandey
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 128
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.Smile
Thanks

Thanks,
Prashant
Sameer Kapur
Sameer Kapur
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 97
Hi, this is really a wonderful article.Smile

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 Smile

Take care
Sameer Kapur
cmrhema
cmrhema
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 247
Many a doubts related to truncate and delete statements got clarified.
Thanks for the article
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search