Why is my transaction being rolled back automatically when INSERT INTO fails?

Question

Surprisingly, I could not find any relevant explanation or documentation for this issue I'm having.

In the case of these SQL statements:

SELECT 1 AS Test INTO #tmpTest    
BEGIN TRAN    
SELECT 1 AS Test INTO #tmpTest    
ROLLBACK TRAN

When executed one by one, the SELECT ... INTO at line 3 fails, as expected, with message -

There is already an object named '#tmpTest' in the database.

However, after that, the ROLLBACK statement in line 4 fails:

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Even though the transaction did BEGIN successfully from line 2.

I've seen SQL Server - transactions roll back on error? but answers don't apply here because the default xact_abort is off. _{In addition, the answer from Quassnoi contradicts the answer by Raj More.}

What's the actual explanation?

Answer 1

Refer to http://www.sommarskog.se/error-handling-I.html

What you are getting is a batch abortion in this case which leads to an implicit rollback. The blog is about SQL Server 2000 error handling, but most of it still remains valid.

Edit: A little more digging and found this which specifically mentions the case of trying to create a table that already exists : http://www.sommarskog.se/error_handling/Part2.html#BatchTranAbort