Interesting... Thanks for saving me the time of trying it myself.

Assuming my memory serves me correctly, this is an inverse of my
experience with the Oracle platform:

In Oracle, we were set up so that no statements committed until
explicitly commanded or the session was ended. However, DDL, and
TRUNCATE in particular, was "fait accompli" (ignoring backup and PIT
recovery).

What I'd like to try and can't any more (no Oracle sandbox to play in),
is to see what happens when an "as of" (or Flashback query) is done on a
truncated object in Oracle.

BTW, does or will MSSQL server have an equivalent to this (Flashback,
that is)?

Paolo Damiani
International Specialty Products

-----Original Message-----
From: Jeremy Greaves
[mailto:mssqldba-ezmlmshield-x1381552.[Email address protected]
Sent: Friday, February 29, 2008 11:21 AM
To: LazyDBA Discussion
Subject: RE SQL Server Truncate Table


"Sleight of hand", not exactly, however it means you get to roll back
your TRUNCATE statement??? With SQL Server (2000, and presumably 2005 -
I've never tried it), you can turn the IMPLICIT_TRANSACTIONS ON or OFF.
It is automatically set to OFF and thus you get the default autocommit
mode upon completion of a transaction. Thus the use of a BEGIN TRAN -
ROLLBACK TRAN - COMMIT TRAN block, if you want to be able to define a
rollback capture event. If you set it to ON then you have to explicitly
COMMIT your statements anyway, so what are you gaining? The fact that
you have to state you want the transaction to commit? Sorry about the
formatting of the reply, but the Mailer Daemon is being a COMPLETE
WANKER today. Which for those of you not au fait with the full range of
Anglo-Saxon is British English for Wanker.
Jeremy Greaves
Fenris Software Consulting, Inc

---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page