Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Difference between Truncate and Delete Expand / Collapse
Author
Message
Posted Thursday, November 22, 2007 7:29 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 17, 2008 9:27 PM
Points: 81, 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
Post #424999
Posted Thursday, November 22, 2007 7:44 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, May 30, 2011 5:15 AM
Points: 782, Visits: 41
Thanks for the clarification.

We are expecting more articles like this from you.
Post #425007
Posted Thursday, November 22, 2007 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2007 6:29 AM
Points: 1, 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).

Post #425018
Posted Thursday, November 22, 2007 9:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 177, Visits: 549
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
Post #425026
Posted Thursday, November 22, 2007 10:26 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 17, 2008 9:27 PM
Points: 81, 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,:)


Thanks,
Prashant
Post #425034
Posted Thursday, November 22, 2007 10:29 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 17, 2008 9:27 PM
Points: 81, 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
Post #425037
Posted Thursday, November 22, 2007 11:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 16, 2008 11:06 PM
Points: 11, 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.
Post #425051
Posted Thursday, November 22, 2007 1:08 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 17, 2008 9:27 PM
Points: 81, 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.:)
Thanks


Thanks,
Prashant
Post #425060
Posted Thursday, November 22, 2007 7:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 1:46 AM
Points: 11, Visits: 76
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
Post #425108
Posted Friday, November 23, 2007 12:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 24, 2013 6:59 AM
Points: 192, Visits: 247
Many a doubts related to truncate and delete statements got clarified.
Thanks for the article
Post #425154
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse