Terminates a query when an overflow or divide-by-zero error occurs during query execution.
SET ARITHABORT {ON | OFF}
If SET ARITHABORT is ON, these error conditions cause the query or 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 occur, a warning message is displayed, but the query, batch, or transaction continues to process as if no error occurred.
Note If neither SET ARITHABORT nor SET ARITHIGNORE is set, Microsoft® SQL Server™ returns NULL and returns a warning message after the query is executed.
When an INSERT, DELETE or UPDATE statement encounters an arithmetic error (overflow, divide-by-zero, or a domain error) during expression evaluation, SQL Server handles these errors as if SET ARITHABORT is ON. The query and the remainder of the batch is aborted and an error message is returned.
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 ARITHABORT is set at execute or run time and not at parse time.
SET ARITHABORT permissions default to all users.
This example demonstrates divide-by-zero and overflow errors with both SET ARITHABORT settings.
-- 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
FROM t1
GO
PRINT '*** Testing divide by zero during INSERT'
GO
INSERT INTO t2
SELECT a/b
FROM t1
GO
PRINT '*** Testing tinyint overflow'
GO
INSERT INTO t2
SELECT a+b
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
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
FROM t1
GO
PRINT '*** Testing tinyint overflow'
GO
INSERT INTO t2
SELECT a+b
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
| SET ARITHIGNORE | SET |