Why does Microsoft SQL Server Implicitly Rollback when a CREATE statement fails?

I am working on pymssql, a python MSSQL driver. I have encountered an interesting situation that I can't seem to find documentation for. It seems that when a CREATE TABLE statement fails, the transaction it was run in is implicitly rolled back:

-- shows 0
select @@TRANCOUNT
BEGIN TRAN

-- will cause an error
INSERT INTO foobar values ('baz')

-- shows 1 as expected
select @@TRANCOUNT

-- will cause an error
CREATE TABLE badschema.t1 (
    test1 CHAR(5) NOT NULL
)

-- shows 0, this is not expect开发者_高级运维ed
select @@TRANCOUNT

I would like to understand why this is happening and know if there are docs that describe the situation. I am going to code around this behavior in the driver, but I want to make sure that I do so for any other error types that implicitly rollback a transaction.

NOTE

I am not concerned here with typical transactional behavior. I specifically want to know why an implicit rollback is given in the case of the failed CREATE statement but not with the INSERT statement.

---

Here is the definitive guide to error handling in Sql Server:
http://www.sommarskog.se/error-handling-I.html

It's long, but in a good way, and it was written for Sql Server 2000 but most of it is still accurate. The part you're looking for is here:
http://www.sommarskog.se/error-handling-I.html#whathappens

In your case, the article says that Sql Server is performing a Batch Abortion, and that it will take this measure in the following situations:

• Most conversion errors, for instance conversion of non-numeric string to a numeric value.
• Superfluous parameter to a parameterless stored procedure.
• Exceeding the maximum nesting-level of stored procedures, triggers and functions.
• Being selected as a deadlock victim.
• Mismatch in number of columns in INSERT-EXEC.
• Running out of space for data file or transaction log.

There's a bit more to it than this, so make sure to read the entire section.

---

It is often, but not always, the point of a transaction to rollback the entire thing if any part of it fails: http://www.firstsql.com/tutor5.htm

One of the most common reasons to use transactions is when you need the action to be atomic:

An atomic operation in computer science refers to a set of operations that can be combined so that they appear to the rest of the system to be a single operation with only two possible outcomes: success or failure. en.wikipedia.org/wiki/Atomic_(computer_science)

It's probably not documented, because, if I understand your example correctly, it is assumed you intended that functionality by beginning a transaction with BEGIN TRAN

---

If you run as one batch (which I did first time), the transaction stays open because the INSERT aborts the batch and CREATE TABLE is not run. Only if you run line-by-line does the transaction get rolled back

You can also generate an implicit rollback for the INSERT by setting SET XACT_ABORT ON.

My guess (just had a light bulb moment as I typed the sentence above) is that CREATE TABLE uses SET XACT_ABORT ON internalls = implicit rollback in practice

Some more stuff from me on SO about SET XACT_ABORT (we use it in all our code because it releases locks and rolls back TXNs on client CommandTimeout)