Have some questions related to transaction management in sql server.
1. What is the difference between having SET XACT_ABORT is ON and having BEGIN TRY ... CATCH ROLLBACK;
2. What if I use only BEGIN TRY ... CATCH ROLLBACK; and do not use XACT_ABORT ON ? Any issues we need to keep in mind?
3. I see XACT_ABORT is a server level setting and is applied to all databases. Can we set it as a specific database level setting?
4. Coming to recent issue in our env app team has come up with is, on one of the DEV server, this setting XACT_ABORT setting is OFF at instance level and so ETL batch loads were giving different results, i mean loads were successful. But on Prod this option is XACT_ABORT is ON at instance level, so ETL batch loads are failing ? question is, which is good ? do we need to turn on this option or turn off to allow batch loads to be run successful?