Does a single rollback affect all transactions? When I run the following script:

begin transaction
    select XACT_STATE()

    select @@TRANCOUNT 

    begin transaction


    select XACT_STATE()

    select @@TRANCOUNT 

    begin transaction


    select XACT_STATE()

    select @@TRANCOUNT 

    rollback

    select XACT_STATE()

    select @@TRANCOUNT 


I get the following result:



So one single rollback statement rolls back any number of transactions?

There is no point in writing the following then:

while @@TRANCOUNT &gt; 0
      rollback
Yes commit and rollback behave differently in this respect.

Commit just decrements the @@trancount. Nothing is actually committed until that reaches zero.

Rollback will always rollback the transaction and set @@trancount to zero.

Just to be clear there is only one transaction going on, irrespective of the value of @@trancount. Nested transactions in SQL Server are a myth. See Paul Randal's blog post:

A SQL Server DBA myth a day: (26/30) nested transactions are real.