Arithabort statement in SQL Server

The arithabort set statement controls where a query should terminate when an arithmetic error occurs.

Arithmetic error may be

Ø  Divided by zero

Ø  Arithmetic overflow.

Syntex

Set Arithabort on/Off

By default, in SQL Server Arithabort is on.

Set Arithabort on : If an arithmetic error occurs the query terminates immediately and return an error. If we are using transaction and within transaction an error occurred then transaction will be rollbacked. Basically, we are using this setting to maintain the data integrity.

Set Arithabort Off : This query continue execution and return a warning instead of an error. It will return null for the problematics computation.  This setting can lead to unexpected results and data inconsistencies.

See the example

set arithabort on  select 1/0

Query terminated.

SET ARITHABORT OFF SET ANSI_WARNINGS  OFF  select 1/0

It is completed successfully without any error. When we are getting the arithmetic error it will return null value.

Note: when we are using the SET ARITHABORT OFF we must need to use SET ANSI_WARNINGS OFF setting as well other wise we this statement will produce error.