RollBack Transaction in SQL Server 2005

SQL Server 2005 newly intoduced the way to rollback the transactions in SQL Server 2005

SET XACT_ABORT ON

when XACT_ABORT is on. The T-SQL statements will automatically rollback the transaction when error occured in statements.

SET XACT_ABORT ON

USE Emp
Begin Tran
Insert into EmpInfo Values(1)
Insert into EmpInfo Values(1) -- This statement will raise the primary key error
Insert into EmpInfo Values(2)
Commit Tran

The first line of statment will execute without any errors. The primary key error will raise in second line. But the entire block transaction will not be commited because the XACT_ABORT in on.