SET ARITHABORT (Transact-SQL)

Terminates a query when an overflow or divide-by-zero error occurs during query execution.

Transact-SQL Syntax Conventions

Syntax

SET ARITHABORT { ON | OFF }
[ ; ]

Remarks

If SET ARITHABORT is ON and SET ANSI WARNINGS is ON, these error conditions cause the query to terminate. If SET ARITHABORT is ON and SET ANSI WARNINGS is OFF, these error conditions cause the batch to terminate. If the errors occur in a transaction, the transaction is rolled back. If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation.

Note

If neither SET ARITHABORT nor SET ARITHIGNORE is set, SQL Server 2005 returns NULL and returns a warning message after the query is executed.

In SQL Server 2005, setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must be explicitly set to ON.

During expression evaluation when SET ARITHABORT is OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.

If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.

If SET ARITHABORT is set to OFF and an abort error occurs during the evaluation of the Boolean condition of an IF statement, the FALSE branch will be executed.

SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see SET Options That Affect Results.

The setting of SET ARITHABORT is set at execute or run time and not at parse time.

Permissions

Requires membership in the public role.

Examples

The following example demonstrates the divide-by-zero and overflow errors that have both SET ARITHABORT settings.

-- SET ARITHABORT
-------------------------------------------------------------------------------
-- Create tables t1 and t2 and insert data values.
CREATE TABLE t1 (
   a TINYINT, 
   b TINYINT
);
CREATE TABLE t2 (
   a TINYINT
);
GO
INSERT INTO t1 
VALUES (1, 0);
INSERT INTO t1 
VALUES (255, 1);
GO

PRINT '*** SET ARITHABORT ON';
GO
-- SET ARITHABORT ON and testing.
SET ARITHABORT ON;
GO

PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab 
FROM t1;
GO

PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab  
FROM t1;
GO

PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2
SELECT a + b AS ab 
FROM t1;
GO

PRINT '*** Resulting data - should be no data';
GO
SELECT * 
FROM t2;
GO

-- Truncate table t2.
TRUNCATE TABLE t2;
GO

-- SET ARITHABORT OFF and testing.
PRINT '*** SET ARITHABORT OFF';
GO
SET ARITHABORT OFF;
GO

-- This works properly.
PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab  
FROM t1;
GO

-- This works as if SET ARITHABORT was ON.
PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab  
FROM t1;
GO
PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2;
SELECT a + b AS ab 
FROM t1;
GO

PRINT '*** Resulting data - should be 0 rows';
GO
SELECT * 
FROM t2;
GO

-- Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
GO

See Also

Reference

SET (Transact-SQL)
SET ARITHIGNORE (Transact-SQL)
SESSIONPROPERTY (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Updated content: Added a statement about how setting ANSI_WARNINGS to ON affects the ARITHABORT setting.