set xact_abort off
begin tran
insert into test_tran values(1)
insert into test_tran values(1)
insert into test_tran values(2)
commit tran
select * from test_tran
/*output:
ID
1
2
*/
truncate table test_tran
-------------------------------------
set xact_abort on
begin tran
insert into test_tran values(1)
insert into test_tran values(1)
insert into test_tran values(2)
commit tran
select * from test_tran
/* NO Records displayed due to the XACT_ABORT ON. This will rollback complete transaction */
NOTE: by default the XACT_ABORT option is OFF