Skip to main content

Roll back truncate command

How many times it has happened that somebody asks u a difference of Truncate and Delete,Atleast i am the person who used to say the very first point is that "TRUNCATE CAN NOT BE ROLLED BACK" untill i came across the blog post of Pinal Dave

So,the best way to understand any point is start with sample,here what we will do is we will create one table and we will create one transaction and under which we will be doing our truncate thing...and finally we will observe the result

Lets us start it by creating sample table and our transaction like following as shown below


CREATE TABLE temp (Name varchar(50))
INSERT INTO temp SELECT 'Moe'
INSERT INTO temp SELECT 'Larry'
INSERT INTO temp SELECT 'Curley'


BEGIN TRAN
   SELECT * FROM temp
   TRUNCATE TABLE temp
   SELECT * FROM temp
ROLLBACK TRAN
   SELECT * FROM temp
   
DROP TABLE temp

Now if you just copy and paste this above in your query editor and hit F5 (RUN),you would be surprise to see that even if you have performed TRUNCATE operation on your table,your data are still available at the end of transaction if we ROLLBACK

I was also got the feeling of learning something new with this helpful post by Pinal,which inspired me to write this post,so that we can use it for further reference

i hope this post is larning for you people as well

comments are welcomed...:-D

Comments

  1. AnandAugust 13, 2010 at 4:29 AM

    @Daivagna,

    this was really strange behavior.

    I couldn't digest the fact tht values are being retrieved even after truncate.

    thanx a lot 4 sharing this strange but correct information.

    keep it up...

    ReplyDelete
  2. ThiruSeptember 29, 2010 at 11:27 AM

    wow mind boggling..
    i searched on google for these kind of Example but i did not get...
    after i saw it i could understand real use of Roll back practically...

    thanks sir.....

    ReplyDelete

Post a Comment

Popular posts from this blog

401.1 Unauthorized with windows authentication error code 0xc000006d

How many of you have faced this hosting issue when you do everything what it takes to run the site with windows authentication but still you are getting the same error again and again? If you think you also have faced the same issue and you tired of reading MSDN KBs for it and still have not found the issue (If KB has solved the issue, well and good, if not you can try this trick),Please Read below Typical scenario In typical hosting with IIS, i did every possible things like enabling windows authentication, changing it in web.config, configuring connection pool, authorization rules, it asks me for window authentication login and despite of entering correct credentials it always fails and keeps on asking for login, and when pressed cancel it gives 401.1 with 0xc000006d error code Solution (Which worked for me at-least after trying for almost 6-9 hrs) You need to change the Loop Back Check in registry so that it allows the host names which you are giving in url are allowed and au...
2 comments
Read more

Set up leprechaun code generation with Sitecore XM Cloud Starterkit

Hi Sitecorians, It has been amazing learning year so far and with the change in technology and shift of the focus on frontend frameworks and composable products, it has been market demand to keep learning and exploring new things. Reasons behind this blog Today's topic is something that was in my draft from April-May, and I always thought that there is already a good documentation out there for  Leprechaun  and a blog post is not needed, Until I realized that there was so many of us facing same kind of issues and same kind of problems and spending same amount of time, That is where I thought, if I could write something which can reduce that repetitive troubleshooting time, That would really help the community. 1)  In a project environment, if we get into some configuration issues, we resolve them, we make sure we are not blocked and continue, but if you think same issue, same step and same scenario will come to other people, so if we can draft it online, it will help othe...
Post a Comment
Read more

Zero to Hero - A real life RCA of exact issue in Sitecore Managed Cloud environment

Hello All, The purpose of today's post is to share a real life burning and escalated scenario which was new to me and how did I approach it and how big the escalations were and what was the outcome Sitecore's goodwill was at stack not because Sitecore is not capable of handling it but just because our environment was Sitecore Managed Cloud, and any issue that comes if its infra, back end code, front end code will be first pointed as Sitecore issue and that is where our consultancy and experience will play a role to prove that it is not Sitecore issue.  Issue we faced Out of the blue our site started giving "504 Gateway Time-out", and it was reported that almost everyone is getting this error, but when we used to browse the site, everything looked good and never 504. 504 Gateway Time-out error tells that, That the request went to Content Delivery servers of Sitecore from gateway, but gateway did not get response in time from those CDs and hence it gave time out error. ...
2 comments
Read more