SET XACT_ABORT is one of the hidden gem we can say. It is helpful in many ways.
Let me explain by an example suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one minute .NET or the code from which you run the query raised a command timeout.
Or we can say you have applied transaction in stored procedure and calling from .NET code and you have mention the command timeout in .NET .The ADO.NET program raised a timeout but still your query is running in background.
Now in such situation sometimes it happened the transaction which is running in SQL SERVER will be open transaction. Which might be a cause of locking problem.
In such situation XACT_ABORT option is helpful.
By default XACT_ABORT option is off you can on it by following statement
SET XACT_ABORT ON;
Now when this statement is on and if you face same situation as mentioned above it will rollback entire batch.
Let me explain with below example
Suppose I wrote following statement “Notice here XACT ABORT is OFF”
Now , when I run the above statements I will get following result in result window
And in the message window we will get following result
What it means instead of the error at least 3 records are inserted but sometimes you require either all or no record commit in the table then let me enable the option XACT_ABORT ON in the query
Now let me re run the statements again. When I run the above statements I got following result in message tab there is no result tab.what is means ??
It means what ever inserted is roll backed when an error occurred in a statement. Although The above example is not the best example of XACT_Abort but it will help to understand how to use it.
I appreciate your inputs on this valuable topic.
Thanks
RJ!!!
