TRUNCATE TABLE and ROLLBACK TRAN
-
Good question ... thanks!
-
Nice to test and show answer as absolute.
Jamie
-
Nice & e@zy
What you don't know won't hurt you but what you know will make you plan to know better -
Ahhh...
I thought Recovery mode has effect while rolling back the transaction.
well new thing to learn today...
Can any give an example of this?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Recovery model has no effect ever on rolling back a transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
kapil190588 (10/11/2012)
Can any give an example of this?Yesterday, when you asked for resources to learrn about SQL Server, one of the suggestions I gave (the first, to be precise) is to make sure you have an instance of SQL Server that you can use to play around with. Now is the time to use that instance.
It is really very easy to create this example yourself. Just create a table, put in a few rows of data, then start a transaction, truncate the table, rollback the transaction and then check the contents of the table.
That example is so basic, that just typing and posting the code would probably have taken me less time than it took me to write this message. But I hope this message has the higher educational value.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Since I worked only in 2005 & above i think got it correct. I don't have idea about earlier versions.
As far as I know anything written between
BEGIN TRAN
.
.
.
ROLLBACK
can be rolled back.
Please correct me if i'm wrong.
--
Dineshbabu
Desire to learn new things.. -
Inserts into a table variable aren't rolled back. "SELECT NEXT VALUE FOR <sequence>" isn't rolled back either. I'm not aware of anything else that isn't but there's likely a small number that aren't.
-
Not just inserts into a table variable, all modifications to table variables are retained after a rollback.
Other things that are not rolled back are assignments to scalar variables, identity values that were givven out are not given out again (so you can get gaps in the identity sequence), and you can still observe the effects of a rolled back transaction in most dynamic management views.
On a more technical side, modifications written to the transaction log are not removed on a rollback (there is in fact an extra entry added, to log the rollback event). And I think (but have not tested) that an autogrow of the database file that takes place because of a huge insert that is rolled back will not be undone.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Page splits don't roll back.
Also, while table variables ignore user transaction rollbacks, statements will still automatically roll back in the case of an error
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks cfradenburg, Hugo & GilaMonster.
I will take the TSQL part, Changes made to Table variables and Scalar variables will not be affected by Rollback. As well as Identity value will not be re-seeded.
--
Dineshbabu
Desire to learn new things..
Viewing 11 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply