Sql Server: A seemingly strange behaviour of BEGIN TRAN - COMMIT First create two tables:

CREATE TABLE xyz (Id INT)
CREATE TABLE abc (Id INT)


Please, observe the following SQL code:

DELETE FROM abc
BEGIN TRAN
EXEC('
INSERT INTO xyz VALUES (1),(2)
DECLARE @x INT = (SELECT Id FROM xyz)
')
INSERT INTO abc VALUES (1)
COMMIT

SELECT * FROM abc


Running it outputs the following error:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.


And there is no result. All as expected.

Now let us change the error to a syntax error by replacing xyz) with xyz. Here is the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'xyz'.


But this time there is a result:



Why? What is going on?
The behaviour depends on SET XACT_ABORT and is explained in the following quote from the linked article.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
OFF is the default setting.