Why doesn’t IMPLICIT_TRANSACTIONS increment @@TRANCOUNT for my IF statement?

I was playing around with SET IMPLICIT_TRANSACTIONS ON. I found a situation where I am convinced the database is automatically creating and committing a transaction without leaving it open. If I do IF EXISTS (SELECT * FROM dbo.t1) or any variation, I am certain that a transaction is happening in the background which I’d expect to stay open if I’m using implicit transactions.

However, after reading the docs, it says something like:


This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction.

and the list it shows does not include IF. However, then I’d expect all statements not found in the documentation’s list to not leave @@TRANCOUNT incremented. So, I looked for another statement not included in that list. I found DECLARE. However, I found that if I perform a subquery with DECLARE, I get the behavior I’d expect from SET IMPLICIT_TRANSATIONS ON. That is, if my DECLARE has a subquery, it increments @@TRANCOUNT if it is zero. So it appears that the documentation is inconsistent with itself

SET IMPLICIT_TRANSACTIONS ON
GO
IF OBJECT_ID('dbo.t1') IS NOT NULL
BEGIN
    DROP TABLE dbo.t1
    COMMIT
END
GO
CREATE TABLE dbo.t1 (x INT);
COMMIT;
GO

IF CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END = 1 PRINT 'unreachable';
PRINT 'after1 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after1 0

DECLARE @x INT = 2;
PRINT 'after2 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after2 0

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;
PRINT 'after3 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after3 1
GO

WHILE @@TRANCOUNT > 0 ROLLBACK
GO

Why does DECLARE increment @@TRANCOUNT for an embedded SELECT but not IF? Is the SELECT embedded in the IF somehow not actually using a transaction?

I’m using SQL Server 13.0.4411.

I suspect that what you're seeing is because some statements get transformed to SELECT statements behind the scenes and others do not. Consider your final DECLARE statement:

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

That is just shorthand for this:

DECLARE @y INT;
SET @y = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

Which can also be written like this:

DECLARE @y INT;
SELECT @y = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

In which case you'd expect to see @@TRANCOUNT incremented. However, can you make the same statement about the IF statement? If I run your original code with actual execution plans turned on I see the following:

I don't see a SELECT in the plan for the first query. I only see COND WITH QUERY. So it seems consistent with the documentation.

StackExchange Database Administrators Q#168409, answer score: 2