Transactions in MS SQL Server

Transaction in unit of code in which if all the statement of code executes successfully then only changes will implemented in database else all the changes will reversed. I.e. it provides check for dependent insert update or deletes operations in database.
If you are new and not aware about this the first question in your mind will be why we need transaction.
Let take an example if you are modifying the designation of employee in employee table and also you want to maintain history for earlier designation in history table. The steps you will
First assign the current designation value in variable
Update new designation in employee table.
Insert row in history table with value in variable.
Suppose the process show error at step no 3 then what will be the result your employee table updated and your history table not. You missed out one designation from employee history table. To avoid such kind of problem and maintain data consistency we use transactions.

There are four possible types of transaction

Auto commit Transaction

Every single statement in transaction. Writing general modification statement without Using Begin transactions in Query analyzer window or db objects. You cannot make any once the changes done.

Explicit Transaction

Each transaction needs to be started by explicitly Begin Transaction Statement and end with commit or rollback statement.

Implicit transactions 

A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

Batch-scoped transactions
It is used for multiple active result sets (MARS) its part of C# coding.



Statements used in transactions
Begin Transaction A
 Modification statements
Commit transactions



@@Trancount
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0,
For ROLLBACK TRANSACTION savepoint_name, it does not change the variable value. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
PRINT @@TRANCOUNT

BEGIN TRAN
    PRINT @@TRANCOUNT “value will be 1”
    BEGIN TRAN
        PRINT @@TRANCOUNT “value will be 2”

ROLLBACK
PRINT @@TRANCOUNT “value will be 0”

Point need to be remembered for direct questions:

Distributed transactions are the transactions in which if there is a stored procedure or database object is running on separate sql server.
Local transactions will automatically convert in distributed transactions when there is any such procedure exists which will run on remote server.

Key words: SQL Server Transcation, What is transaction , Implement transaction in SQL