XACT_ABORT { ON | OFF }

SET value XACT_ABORT, It is used for run time error handling. as per  msdn It Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

If we are setting ON then in case of run-time error entire transaction will be terminated and rolled back, see it with example.

CREATE TABLE TestTable (Column1 INT NOT NULL)

GO

Now we just need to test it with a run-time error like

TRUNCATE TABLE TestTable

GO

SET XACT_ABORT ON

GO

BEGIN TRAN

insert into TestTable values (1)

insert into TestTable values (NULL)

insert into TestTable values  (3)

COMMIT TRAN

GO

SELECT * FROM TestTable

GO

And the result will be 

So no row inserted in table entire transaction terminated and rolled back, now lets check same with OFF 

TRUNCATE TABLE TestTable

Go

SET XACT_ABORT OFF --Optional as OFF is default value

GO

BEGIN TRAN

insert into TestTable values (1)

insert into TestTable values (NULL)

insert into TestTable values  (3)

COMMIT TRAN

GO

SELECT * FROM TestTable

GO

Now check the result

Both records inserted only except one which leads to error, Only the statement that raised the error is rolled back and the transaction continues processing. According to msdn

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

It is useful when we need to rollback entire transaction if there is any error.