For statements that are allowed to execute within an explicit transaction (i.e.
BEGIN TRAN, or heaven forbid
ON), I am only aware of the following two that will not be affected by a
ROLLBACK (though technically both are somewhat "cheating" in a sense):
- DML statements against table variables (variable, even table variables, do not participate in transactions)
statements executed via a linked server and
OPENQUERY (even if using a loop back server definition to connect to the current instance) if you have the linked server property of "remote proc transaction promotion" disabled (so that it doesn't attempt to enlist in the current transaction).
OPENQUERY (with that one option disabled) works because it makes a separate connection. There are other, similar methods of making a separate connection within T-SQL. David Browne, in a comment, mentioned extended stored procedures. We can also add SQLCLR methods (no matter what T-SQL object type they are exposed as) to that list, but only if making a regular / external connection, and specifying "
enlist=false;" in the connection string. We can probably also add the OLE Automation stored procedures (i.e.
sp_OA*), but I am not 100% sure of these.
- There might be one or two statements that can be executed within an explicit transaction but cannot be rolled back, and I think I maybe ran into that many years ago, but I don't recall what it is and could have been user error (some other user, of course ;-), so you would have to consult the documentation for specifics of each statement / operation and/or test as many as you can (sometimes the documentation is incorrect ... it happens).
Not sure if this counts or not as it isn't an explicit change, but is not something that goes back to what it was before the transaction started (because it doesn't even make sense that it could): Tibor Karaszi noted in a comment that "consuming an identity/sequence value" could qualify. I have tested and confirmed that sequences do not give back the requested values.
Similarly, another value that is database state, and not specifically user data, that is not affected by a rollback is the current / last-used timestamp value (this is per-database and is returned by the system variable
@@DBTS; also confirmed via test)
- Not sure if these count or not as they are neither an explicit change nor even the state of the database, but their values are not affected by a rollback: in a comment, David Browne mentioned
SESSION_CONTEXT, which is a property of each session. Along those same lines, we can include the related / similar
if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back?
Yes, this is the purpose of explicit transactions: to group statements together into an atomic unit of work.
Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW? What about DROP DATABASE?
Some (maybe most) yes. Some no. You probably should test. For example, here is some DDL that does roll-back:
IF (OBJECT_ID(N'dbo.DropMe') IS NOT NULL)
DROP TABLE dbo.DropMe;
CREATE TABLE dbo.DropMe (col1 INT);
DROP TABLE dbo.DropMe;
SELECT * FROM dbo.[DropMe];
But, conversely, here is some DDL that cannot be executed within an explicit (or implicit) transaction:
CREATE DATABASE [DropMe];
DROP DATABASE [DropMe];
Msg 574, Level 16, State 0, Line XXXXX
DROP DATABASE statement cannot be used inside a user transaction.
So, just take the two examples above and rework them for other statements that you are either curious about or need a definitive answer on.