There is no inherent transactional quality to T-SQL batches. As you have seen already, unless you provide the syntax to define a single transaction made up of several statements, each individual statement in a batch is its own separate transaction, and each statement is carried to completion or fails individually.
The failure of a transaction within a batch typically does not cause the batch to stop processing (one exception is a rollback in a
TRIGGER if you are not using
TRY...CATCH logic). In other words, in most cases, transaction flow does not affect process flow. After a
ROLLBACK TRAN statement, processing continues with the next statement in the batch or stored procedure. For this reason, you should be sure ...