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