Use of XACT_ABORT ON

SET XACT_ABORT
As I told in my previous article (http://dotnetpen.blogspot.com/2011/11/exception-handling-in-sql-server.html) I am bak with small article on XACT_ABORT

XACT_ABORT is a command in sql server . When set to ‘ON’ it will rollback any transaction if an error occurred in it.If used where there is no transaction it will just roll back the statement where error occurred and aborts execution.

When a timeout occurs in client or client presses cancel button catch block wont get called . In this case the connection remains open and the transaction also becomes uncommitable.
If we use SET XACT_ABORT to ON then these situation is taken care and sql server roll backs any current transaction.

syntax:
SET XACT_ABORT ON;

please note that it helps to rollback explicit transactions. But we cant handle exceptions ourselves as we do in catch block. Also if used in stored procedures where no transaction is used , it will just stops executing statements after the error generating statement.But any statement before the error generating statement will get commited .

play with below sample to explore more....
eg.
SET NOCOUNT ON;
SET XACT_ABORT ON;  
          BEGIN TRAN
               BEGIN TRY
--RAISERROR('error happened',16,1);                     
              INSERT INTO t1 VALUES(22);
              INSERT INTO t1 VALUES(null);
              COMMIT
          END TRY
         BEGIN CATCH
                    IF XACT_STATE()<>0
                              ROLLBACK 
        END CATCH             

 It is a good practice to set it 'ON' in every stored procedure where transaction is used.                      

Exception Handling in SQL SERVER

First of all let us understand where exception handling can be useful in sqlserver.
I am listing 2 main purpose of it.
1. In transactions it is a must .
2.To return the client some useful/custom error message.

Untill SQL SERVER 2003 exception handling was done by using system variable @@ERROR.But it has its own disadvantages.
EG.
       BEGIN TRANSACTION
               DELETE FROM tablestaff WHERE staffid>30
               IF @@ERROR <> 0
                    BEGIN
                         ROLLBACK
                         RAISERROR ('Error in staff', 16, 1)
                         RETURN
                   END
               DELETE FROM tableEmp WHERE empid<70
               IF @@ERROR <> 0
                   BEGIN
                      ROLLBACK
                      RAISERROR ('Error in Emp', 16, 1)
                      RETURN
                   END
         -- Commit the transaction....
              COMMIT
As seen from above code we can list out major disadvantages of handling exception using @@ERROR variavle .
1. One has to make sure that he/she is not missing to check @@ERROR afterany DML statement.Else we may miss some part while doing error handling which may cause transaction related errors.
2.Stored procedures becomes too long.
3.Diffcult to know exactline number at which error occured. etc....

REMEADY:
SQL SERVER 2005 inducted TRY....CATCH block, much similar to what we are used to in any modern object oriented language.
The above code can be rewritten using TRY...CATCH block as...
          BEGIN TRAN
                BEGIN TRY
                    DELETE FROM tablestaff WHERE staffid>30
                    DELETE FROM tableEmp WHERE empid<70
                    COMMIT
               END TRY
               BEGIN CATCH
                    IF XACT_STATE()<>0
                    ROLLBACK
                    RAISERROR ('Error in line %d, ERROR_SEVERITY(), ERROR_STATE(),ERROR_LINE())
                   END CATCH

Simple stuff !!!(ll explain the functions below soon)
I think the construct is straight forward to be understood.Now lets see what XACT_STATE() stands.
XACR_STATE can have 3 values:
0- No transaction in place currently.
1-Current transaction is a commitable transaction.It means it can be commited.
-1-Current transaction is uncommitable.
Eg:
             BEGIN TRAN
                       BEGIN TRY
                               DELETE FROM tablestaff WHERE staffid>30
                               SELECT 1/0
                               COMMIT
                      END TRY
                      BEGIN CATCH
--XACT_STATE=1 , because error occured when performing a select statement and we can
--commit it in some senarios as this one with no affect to DB table.
                     END CATCH

      If error would have occured while say insertin / deleting operation then XACT_STATE() would have been -1, meaning u cant commit the transaction in any means.

    BEGIN TRAN
                       BEGIN TRY
                               DELETE FROM tablestaff WHERE staffid>30
                               INSER INTO tablestaff values('Enter an int')
                               COMMIT
                      END TRY
                      BEGIN CATCH
--XACT_STATE=-1 , because error occured when performing a insert statement and we can
--commit it in some senarios as this one with no affect to DB table.
                     END CATCH

IMP: If XACT_STATE() is -1 then user cannot perform any operation in database that will affect the current transaction log. These operation can be an insert statement, droping table,rolling back to a saving point etc.Only after rollback he/she can perfor these tasks.

@@TRANCOUNT:
If you see articles in web , many have used this variable instead of XACT_STATE() for rollback. But I felt using XACT_STATE is more appropriate. You can do your own analisys.
@@TRANCOUNT increment by 1 on begin transaction statement. On commit decrements by 1.
On rollback becomes 0.
So it can be used as below in catch block.
IF @@TRANCOUNT>0
ROLLBACK

RAISERROR:It helps to notify the client abt the details of the error , like error message, and othe details passed on to the argument.So if the above SP's called by an .net application , the .net application can able to know where exactly in database error occured.

The syntax is :

RAISERROR(msg id | msg text , severity,state ,<user defined variables>)

Msg id is the error number of the error occurred.It should be greater than 50000 when created by user.By default when we raise errors msg id  generated is 50000.

Now the functions used in catch block.
        ERROR_SEVERITY(): Returns severity level of the error.
        ERROR_NUMBER():returns same as @@ERROR
        ERROR_LINE(): line at which the error occured
        ERROR_PROCEDURE():sp name where error occured
        ERROR_MESSAGE(): full error message that got generated
These help in letting the client know where error has occured.
eg.
SELECT @sev=ERROR_SEVERITY()
SELECT @state=ERROR_STATE()
SELECT @line=ERROR_LINE()
SELECT @msg=ERROR_MESSAGE()
 RAISERROR('error occured at line %d. The error message is : %s',@severity,@state,@line,@msg)

Note the identifiers '%d' '%s' work similar to printf statement in 'C'.

NOTE: These functions will work only in catch block.Elsewhere it will return null.

There is another feature where we can add command 'SET XACT_ABORT' to 'ON' for automatic rollback when an error occurs.This I will explain in a later article.

Also in the next version of sql server code named 'Denali'  a new keyword 'throw' has been added which overcomes some shortcomings of RAISERROR. I have not yet got a chance to try it as I dont have sql server 2012 :-(.

So next time you go for exception handling , keep in mind all these features .

NVarchar(MAX)/Varchar(Max) in sql server

Quite often we come across datatypes in sql server with a 'Max' as a size !!!
Lets first understand what it stand for.In sql server varchar can have 8000 characters(=8000 bytes), Nvarchar 4000 characters(1 unicode char=2 bytes).In sql server 2005 a new keyword 'MAX' is added aiming to replace older datatypes like TEXT,NTEXT,IMAGE etc.The size of data it can hold is 2 GB , the same size that can be hold by TEXT,NTEXT etc.

Problem with TEXT,NTEXT,IMAGE:
In sql server these 3 datatypes are used as BLOBs(Binary Large Objects).What it means data belonging to these datatypes stored in collection of 8 kb datapages that are separate from datapages that store other data in the same table.A pointer is stored in the original column .So when we try to read the data from table , a LOB table scan(LOB datapages are read) is performed which is expensive.

With Varchar(MAX) data is stored in the same datapages as used by other columns.(of course upto 8kb it is fine , above it sqlserver uses some datapages called as overflow page.But accessing these pages are handled well by sqlserver and no lob datapages needed.).So simple table scan is performed when select statement is used.(faster than a LOB scan)

So when programming sql server we should avoid the use of these BLOB types as these will be removed in future editions of sql server.

An Example:
Take two tables Table_1 and Table_2

Table_1: LobColumn ntext
Table_2:MaxColumn nvarchar(max)

now see the below analisys after inserting say 26 rows

SET STATISTICS IO ON
Select * from Table_1
(26 rows affected)
Table 'Table_1'.Scan count 1 ,logical read 1,physical reads 0, read-ahead reads 0,lob logical reads 52,lob physical reads 0,lob read ahead reads 0

SET STATISTICS IO ON
Select * from Table_2
(26 rows affected)
Table 'Table_2'.Scan count 1 ,logical read 1,physical reads 0, read-ahead reads 0,lob logical reads 0,lob physical reads 0,lob read ahead reads 0

Clearly for Table_2 scan is faster as there is no LOB data read.

Apart from it sql server does not allow BLOB types to be used in stored procedure/user defined functions.Also string library functions like REPLACE etc are not allowd to be used with BLOB types.
NOTE:Always use Varchar unless you need to store multilinguial characters, in which case use Nvarchar.Remember Nvarchar takes more memory than varchar.

How to use MAX datatypes in Stored Procedures:
Consider following statements in a stored procedure :

Declare @maxData nvarchar(max)
SET @maxData='-----some statements--------'

When we use statement in this way , after 4000 chars this will throw exception saying nvarchar can store maximum 4000 characters.
But in principle it should have stored 2GB chars right !!!!
Here LHS is of type nvarchar(max) , RHS is of type simple nvarchar(which can store 4000 bytes).The workaround of the problem is break @maxData and use as below:

@maxData='-----some statements----'
@maxData=@maxData+'-----some statements----'
@maxData=@maxData+'-----some statements----'

In each statement RHS should have a new string with maximum 4000 chars.

(This is a pratical problem I faced in one of my project and found it helpful)
Happy Sql Programming :-)
Please leave an honest comment so that I can improve on my blogging aswell as technical skill :)