引用MSDN:

当SET XACT_ABORT设置为ON时，如果Transact-SQL语句引发运行时错误，则整个事务将被终止并回滚。 当SET XACT_ABORT为OFF时，在某些情况下，只有引发错误的Transact-SQL语句被回滚，事务继续处理。

在实践中，这意味着一些语句可能会失败，使事务“部分完成”，并且对于调用者来说可能没有此失败的迹象。

举个简单的例子:

INSERT INTO t1 VALUES (1/0)    
INSERT INTO t2 VALUES (1/1)    
SELECT 'Everything is fine'

这段代码在XACT_ABORT OFF时将“成功”执行，在XACT_ABORT ON时将以错误终止('INSERT INTO t2'将不会执行，客户端应用程序将引发异常)。

作为一种更灵活的方法，您可以在每个语句之后检查@@ERROR(老式方法)，或者使用TRY…CATCH块(MSSQL2005+)。我个人更喜欢在没有必要进行高级错误处理时设置XACT_ABORT ON。

引用MSDN:

当SET XACT_ABORT设置为ON时，如果Transact-SQL语句引发运行时错误，则整个事务将被终止并回滚。 当SET XACT_ABORT为OFF时，在某些情况下，只有引发错误的Transact-SQL语句被回滚，事务继续处理。

在实践中，这意味着一些语句可能会失败，使事务“部分完成”，并且对于调用者来说可能没有此失败的迹象。

举个简单的例子:

INSERT INTO t1 VALUES (1/0)    
INSERT INTO t2 VALUES (1/1)    
SELECT 'Everything is fine'

这段代码在XACT_ABORT OFF时将“成功”执行，在XACT_ABORT ON时将以错误终止('INSERT INTO t2'将不会执行，客户端应用程序将引发异常)。

作为一种更灵活的方法，您可以在每个语句之后检查@@ERROR(老式方法)，或者使用TRY…CATCH块(MSSQL2005+)。我个人更喜欢在没有必要进行高级错误处理时设置XACT_ABORT ON。

SET XACT_ABORT ON指示SQL Server在发生运行时错误时回滚整个事务并中止批处理。它涵盖了在客户端应用程序上而不是SQL Server本身(默认的XACT_ABORT OFF设置不包括)发生命令超时的情况。

由于查询超时将使事务保持打开状态，建议在所有具有显式事务的存储过程中使用SET XACT_ABORT ON(除非您有特定的理由不这样做)，因为应用程序在具有打开事务的连接上执行工作的后果是灾难性的。

丹·古兹曼的博客上有一篇很棒的概述，

在这里添加新的更新。最新的MSDN更新显示了如何使用XACT_ABORT ON和TRY/CATCH Block。MSDN链接

    -- Check to see whether this stored procedure exists.  
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL  
    DROP PROCEDURE usp_GetErrorInfo;  
GO  
  
-- Create procedure to retrieve error information.  
CREATE PROCEDURE usp_GetErrorInfo  
AS  
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE () AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  
GO  
  
-- SET XACT_ABORT ON will cause the transaction to be uncommittable  
-- when the constraint violation occurs.   
SET XACT_ABORT ON;  
  
BEGIN TRY  
    BEGIN TRANSACTION;  
        -- A FOREIGN KEY constraint exists on this table. This   
        -- statement will generate a constraint violation error.  
        DELETE FROM Production.Product  
            WHERE ProductID = 980;  
  
    -- If the DELETE statement succeeds, commit the transaction.  
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE usp_GetErrorInfo;  
  
    -- Test XACT_STATE:  
        -- If 1, the transaction is committable.  
        -- If -1, the transaction is uncommittable and should   
        --     be rolled back.  
        -- XACT_STATE = 0 means that there is no transaction and  
        --     a commit or rollback operation would generate an error.  
  
    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  
  
    -- Test whether the transaction is committable.
    -- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error.
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  
GO

Regarding client timeouts and the use of XACT_ABORT to handle them, in my opinion there is at least one very good reason to have timeouts in client APIs like SqlClient, and that is to guard the client application code from deadlocks occurring in SQL server code. In this case the client code has no fault, but has to protect it self from blocking forever waiting for the command to complete on the server. So conversely, if client timeouts have to exist to protect client code, so does XACT_ABORT ON has to protect server code from client aborts, in case the server code takes longer to execute than the client is willing to wait for.

它用于事务管理，以确保任何错误都会导致事务回滚。