USE OF XACT_ABORT

With use of XACT_ABORT we can manage either entire transaction will rollback or
only statement which raises error

When XACT_ABORT is ON entire transaction will rollback in case of run time error



When XACT_ABORT is OFF depends of severity either the transaction will rollback or commit
OFF is default setting

This setting is set at runtime or execute time.

Lets create a  table and insert data into it
We will generate arithmatic overflow error

CREATE TABLE temp

    (

         id   INT,

         data VARCHAR(100)

    )

First we will set XACT_ABORT OFF

SET XACT_ABORT OFF

TRUNCATE TABLE temp

BEGIN TRAN

INSERT INTO temp

VALUES      (1,'a')

INSERT INTO temp

VALUES      (123432232333,'a')

COMMIT TRAN

Here we can see first insert statement committed but second one only rollback.
Not entire transaction

SELECT *

FROM   temp

Now we will set XACT_ABORT ON

SET XACT_ABORT ON

TRUNCATE TABLE temp

BEGIN TRAN

INSERT INTO temp

VALUES      (1,'a')

INSERT INTO temp

VALUES      (123432232333,'a')

COMMIT TRAN

Here when error comes entire transcation will be rollback

SELECT *

FROM   temp