SET ARITHABORT ON
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization, leading to performance issues.
Warning
The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF might receive different query plans, making it difficult to troubleshoot poorly performing queries. That is, the same query might execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio, always match the client ARITHABORT setting.
When SET ARITHABORT and SET ANSI WARNINGS are ON, these error conditions cause the query to end.
When SET ARITHABORT is ON and SET ANSI WARNINGS is OFF, these error conditions cause the batch to end. If the errors occur in a transaction, the transaction is rolled back. When SET ARITHABORT is OFF and one of these errors occurs, a warning message appears and the result of the arithmetic operation is NULL.
If SET ARITHABORT and SET ANSI WARNINGS are OFF and one of these errors occurs, a warning message appears, and the result of the arithmetic operation is NULL.
If neither SET ARITHABORT nor SET ARITHIGNORE is ON, SQL Server returns NULL and a warning message appears after the query runs.
When ANSI_WARNINGS has a value of ON and the database compatibility level is set to 90 or higher then ARITHABORT is implicitly ON regardless of its value setting. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must be explicitly set to ON.
For expression evaluation, if SET ARITHABORT is OFF and an INSERT, UPDATE, or DELETE statement comes across an arithmetic, overflow, divide-by-zero, or domain error, SQL Server inserts or updates a NULL value. If the target column isn't nullable, the insert or update action fails and the user sees an error.
When 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.
When SET ARITHABORT is OFF and an abort error occurs during the evaluation of the Boolean condition of an IF statement, the FALSE branch executes.
SET ARITHABORT must be ON when you're 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 fail.
The setting of SET ARITHABORT happens at execute or run time and not at parse time.
To view the current setting for SET ARITHABORT, run the following query:
DECLARE @ARITHABORT VARCHAR(3) = 'OFF';
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';
SELECT @ARITHABORT AS ARITHABORT;
Permissions
Requires membership in the public role.
Examples
The following example demonstrates the divide-by-zero and overflow errors that have 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
INSERT INTO t1
VALUES (1, 0);
INSERT INTO t1
VALUES (255, 1);
PRINT '*** SET ARITHABORT ON';
-- SET ARITHABORT ON and testing.
SET ARITHABORT ON;
PRINT '*** Testing divide by zero during SELECT';
SELECT a / b AS ab
FROM t1;
PRINT '*** Testing divide by zero during INSERT';
INSERT INTO t2
SELECT a / b AS ab
FROM t1;
PRINT '*** Testing tinyint overflow';
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
PRINT '*** Resulting data - should be no data';
SELECT *
FROM t2;
-- Truncate table t2.
TRUNCATE TABLE t2;
-- SET ARITHABORT OFF and testing.
PRINT '*** SET ARITHABORT OFF';
SET ARITHABORT OFF;
-- This works properly.
PRINT '*** Testing divide by zero during SELECT';
SELECT a / b AS ab
FROM t1;
-- This works as if SET ARITHABORT was ON.
PRINT '*** Testing divide by zero during INSERT';
INSERT INTO t2
SELECT a / b AS ab
FROM t1;
PRINT '*** Testing tinyint overflow';
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
PRINT '*** Resulting data - should be 0 rows';
SELECT *
FROM t2;
-- Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
See Also
SET Statements (Transact-SQL)
SET ARITHIGNORE (Transact-SQL)
SESSIONPROPERTY (Transact-SQL)
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for
This product
