title: Why does SQL Server default XACT_ABORT to OFF? Can it be set to ON globally?
body: <p>I understand the purpose of <a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql" rel="noreferrer">SET XACT_ABORT</a> command:</p>
  
  <blockquote>
    <p>When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
    run-time error, the entire transaction is terminated and rolled back.</p>
    
    <p>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.</p>
  </blockquote>
  
  <p>In general, the scenarios where one would want to continue processing a transaction if error would be outnumbered by the scenarios where one would want to rollback the entire transaction if error, because I often hear DBAs and blogs recommending to keep <code>SET XACT_ABORT ON</code> to avoid running into inconsistent transaction results. I have seen all our stored procedures also have <code>SET XACT_ABORT ON</code>, as part of the templated-code.</p>
  
  <p>Quesions:</p>
  
  <ol>
  <li><p>If most of the use cases require <code>XACT_ABORT</code> to be <code>ON</code>, what would have lead SQL Server to default it to <code>OFF</code>? Would defaulting <code>XACT_ABORT</code> to <code>ON</code> had added any overhead to SQL Server transaction processing?</p></li>
  <li><p>The <code>SET XACT_ABORT</code> command affect only the current sessions. I know I can make SSMS default to <code>SET XACT_ABORT ON</code> from <code>SSMS &gt; Tools &gt; Options &gt; Query Execution &gt; SQL Server &gt; Advanced</code> as shown in this image:
  <a href="https://i.stack.imgur.com/6JEwv.png" rel="noreferrer"><img src="https://i.stack.imgur.com/6JEwv.png" alt="enter image description here"></a>
  But this is limited to only for the SQL statements running through SSMS and does not help with stored procedures called through application code/SSIS packages . For those procedures I still have to repeat <code>SET XACT_ABORT ON</code> in every procedure. Is there any way <code>XACT_ABORT</code> can be set to <code>ON</code> at database level? Any other ways I can set the <code>XACT_ABORT</code> globally and don't have to worry about it every time?</p></li>
  </ol>