Am I guaranteed that TRUNCATE TABLE always can be rolled back within a transaction?
in [Programming SQL]
|
Prev: "Not Null Default" column behavior change in SQL 2K and SQL 2005
Next: Am I guaranteed that TRUNCATE TABLE always can be rolled back within a transaction?
From: Igor on 18 Jul 2008 13:05 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
From: SQL Menace on 18 Jul 2008 14:00 Check out Paul Randal's post here: http://www.sqlskills.com/blogs/paul/2007/12/05/SearchEngineQA10WhenArePagesFromATruncatedTableReused.aspx Denis The SQL Menace http://www.lessthandot.com/ http://sqlservercode.blogspot.com http://sqlblog.com/blogs/denis_gobo/default.aspx On Jul 18, 1:05 pm, Igor <igor.bin...(a)gmail.com> wrote: > 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
From: Igor on 18 Jul 2008 14:36 Thank you very much, Roy. This is what I was looking for: simple and straight answer. -igor
From: Igor on 18 Jul 2008 14:37
Thanks, SQL Menace, for the link. It does help alot. -igor |