— SET ARITHABORT AND SET ANSI_WARNINGS
——————————————————————————-
— 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; SET ANSI_WARNINGS ON’
GO
— SET ARITHABORT ON and  AND SET ANSI_WARN ON AND testing.
SET ARITHABORT ON
SET ANSI_WARNINGS 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 SET ANSI_WARNINGS OFF and testing.
PRINT ‘*** SET ARITHABORT OFF;SET ANSI_WARNINGS OFF;’
GO
SET ARITHABORT OFF;
SET ANSI_WARNINGS 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’;
GO
SELECT *
FROM t2;
GO

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.