Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Programming / SQL / July 2008

Tip: Looking for answers? Try searching our database.

Am I guaranteed that TRUNCATE TABLE always can be rolled back within     a transaction?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Igor - 18 Jul 2008 18:05 GMT
Is the statement below always true?

I can use TRUNCATE TABLE as in the example below and I am guaranteed
that if INSERT fails for some
reason, table Result will be restored to its initial (before the
transaction began) state?

In other words, can I rely on the fact that the transaction scope
prevents the pages from being reused (assuming there are no nested
transactions)?

BEGIN TRY
     BEGIN TRANSACTION
           TRUNCATE TABLE Result
           INSERT INTO Result
           SELECT * FROM Work
     COMMIT TRANSACTION
END TRY
BEGIN CATCH
     IF XACT_STATE() <> 0
           ROLLBACK TRANSACTION;
END CATCH

Thanks,

-Igor Binder
Roy Harvey (SQL Server MVP) - 18 Jul 2008 18:52 GMT
When TRUNCATE is used inside a transaction, if the transaction is
rolled back the TRUNCATE is rolled back too.  So TRUNCATE is safe
within a transaction.

I took a quick look at your code and it appears to be fine, but I did
not test it.

Roy Harvey
Beacon Falls, CT

>Is the statement below always true?
>
[quoted text clipped - 22 lines]
>
>-Igor Binder
Igor - 18 Jul 2008 19:36 GMT
Thank you very much, Roy.

This is what I was looking for: simple and straight answer.

-igor
SQL Menace - 18 Jul 2008 19:00 GMT
Check out Paul Randal's post here:
http://www.sqlskills.com/blogs/paul/2007/12/05/SearchEngineQA10WhenArePagesFromA
TruncatedTableReused.aspx


Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

> Is the statement below always true?
>
[quoted text clipped - 22 lines]
>
> -Igor Binder
Igor - 18 Jul 2008 19:37 GMT
Thanks, SQL Menace, for the link. It does help alot.
-igor
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.