I am running a transactional query in SQL Server, and I want to turn XACT_ABORT on, so I write: SET XACT_ABORT ON.
First, in such case, would I have overwritten other settings done by another query? I mean, are those options scoped at the query level or at the connection level? Perhaps at the server level?
If you execute an explicit ad-hoc SET XACT_ABORT ON, the setting remains in effect for the duration of the current session. Other session settings will remain unchanged.
In the case of SET XACT_ABORT within a module (e.g. stored procedure or trigger), the setting is effective only within the scope of module execution and reverts to the original session setting afterwards.
If the application doesn't explicitly execute SET XACT_ABORT ON or SET XACT_ABORT OFF, the server user options configuration value may specify SET XACT_ABORT ON as the server-wide default by turning on bit 16384. The default configuration value is 0.
The SET Statements documentation describes general behavior of SET statements. Other settings (not including XACT_ABORT) can be turned ON (but not OFF) at the database level to determine default session settings for that session database context.
