Export (0) Print
Expand All
Expand Minimize

SET ARITHIGNORE (Transact-SQL)

Controls whether error messages are returned from overflow or divide-by-zero errors during a query.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

SET ARITHIGNORE { ON | OFF }
[ ; ]

The SET ARITHIGNORE setting only controls whether an error message is returned. SQL Server returns a NULL in a calculation involving an overflow or divide-by-zero error, regardless of this setting. The SET ARITHABORT setting can be used to determine whether the query is terminated. This setting does not affect errors occurring during INSERT, UPDATE, and DELETE statements.

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.

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

To view the current setting for this setting, run the following query.

DECLARE @ARITHIGNORE VARCHAR(3) = 'OFF';
IF ( (128 & @@OPTIONS) = 128 ) SET @ARITHIGNORE = 'ON';
SELECT @ARITHIGNORE AS ARITHIGNORE;

Requires membership in the public role.

The following example demonstrates using both SET ARITHIGNORE settings with both types of query errors.

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF
GO

PRINT 'Setting ARITHIGNORE ON';
GO
-- SET ARITHIGNORE ON and testing.
SET ARITHIGNORE ON;
GO
SELECT 1 / 0 AS DivideByZero;
GO
SELECT CAST(256 AS TINYINT) AS Overflow;
GO

PRINT 'Setting ARITHIGNORE OFF';
GO
-- SET ARITHIGNORE OFF and testing.
SET ARITHIGNORE OFF;
GO
SELECT 1 / 0 AS DivideByZero;
GO
SELECT CAST(256 AS TINYINT) AS Overflow;
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft