What is the benefit of using “SET XACT_ABORT ON” in a stored procedure?

by

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. It covers you in cases like a command timeout occurring on the client application rather than within SQL Server itself (which isn’t covered by the default XACT_ABORT OFF setting.)

Since a query timeout will leave the transaction open, SET XACT_ABORT ON is recommended in all stored procedures with explicit transactions (unless you have a specific reason to do otherwise) as the consequences of an application performing work on a connection with an open transaction are disastrous.

There’s a really great overview on Dan Guzman’s Blog,

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. How to select just the row in the second table?
  4. SQL update from one Table to another based on a ID match
  5. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  6. SQL JOIN vs IN performance?
  7. How important is the order of columns in indexes?
  8. Why no windowed functions in where clauses?
  9. T-SQL Cast versus Convert
  10. SQL Case Sensitive String Compare
  11. What is a stored procedure?
  12. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  13. Exporting data In SQL Server as INSERT INTO
  14. convert Excel Date Serial Number to Regular Date
  15. Convert Comma Separated column value to rows
  16. Partition Function COUNT() OVER possible using DISTINCT
  17. SQL SERVER: Get total days between two dates
  18. Alter user defined type in SQL Server
  19. Deferrable Constraints in SQL Server
  20. What does sp_reset_connection do?
  21. SQL Server equivalent to MySQL enum data type?
  22. Create parameterized VIEW in SQL Server 2008
  23. How to correctly insert newline in nvarchar [duplicate]
  24. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  25. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  26. How do I change db schema to dbo
  27. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  28. How to get the next number in a sequence
  29. How to Select Every Row Where Column Value is NOT Distinct
  30. How to insert into a table with just one IDENTITY column?

Leave a Comment