Export (0) Print
Expand All

SET ARITHABORT

SQL Server 2000

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

Syntax

SET ARITHABORT { ON | OFF }

Remarks

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 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, 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 when SET ARITHABORT is OFF, 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.

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

SET ARITHABORT must be ON when creating or manipulating 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 Considerations When Using SET Statements in SET.

Permissions

SET ARITHABORT permissions default to all users.

Examples

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

See Also

SET

SET ARITHIGNORE

Show:
© 2014 Microsoft