Nested Transaction in SQL Server

A Nested Transaction in SQL Server occurs when a BEGIN TRAN statement is executed inside another active transaction. However, SQL Server does not create independent physical transactions. Instead, it maintains a transaction counter called @@TRANCOUNT. Each BEGIN TRAN increases the count, and each COMMIT decreases it. The actual commit occurs only when @@TRANCOUNT reaches zero. A key interview point is that an inner COMMIT does not permanently save data; it merely decreases the transaction count. Similarly, a ROLLBACK issued at any nested level rolls back the entire transaction and resets @@TRANCOUNT to zero. Therefore, SQL Server nested transactions are logical constructs rather than true independent transactions. For partial rollback requirements, SAVEPOINTs should be used instead of nested transactions.

Syntex

BEGIN TRAN OuterTran

    BEGIN TRAN InnerTran

    COMMIT TRAN InnerTran

COMMIT TRAN OuterTran

This looks like two transactions, but internally SQL Server treats them as ONE Physical Transaction Multiple Logical Levels.  We use nested transactions primarily for modularity, code reusability, and stored procedure independence. They allow multiple stored procedures to participate in the same transaction without knowing whether they were called directly or by another procedure.

Nested transactions are not used for independent commits. 

Nested transactions provide logical transaction boundaries and allow reusable stored procedures to participate in a larger transaction. They help manage transaction ownership and modularize code, but they do not provide independent commits. Only the outermost COMMIT permanently commits the transaction. For partial rollback requirements, SAVEPOINT should be used instead.