Disabling "ANSI_WARNINGS" and/or "ARITHABORT" in a procedure may silence errors, decrease performance, or block index creation.
From the documentation (ARITHABORT, ANSI_WARNINGS), disabling these options could result
in (among others):
SET ANSI_WARNINGS OFF
- CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail
- No warning issued if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT
- The divide-by-zero and arithmetic overflow errors cause null values to be returned, no roll-back
SET ARITHABORT OFF
- It can negatively impact query optimization, leading to performance issues
- An arithmetic, overflow, divide-by-zero, or domain error, during INSERT, UPDATE, or DELETE statement will cause SQL Server to insert or update
a NULL value
This rule raises an issue when "ANSI_WARNINGS" and/or "ARITHABORT" are set to OFF in a procedure.
Noncompliant code example
CREATE PROCEDURE myProc
AS
BEGIN
SET ANSI_WARNINGS OFF; -- Noncompliant
SET ARITHABORT OFF; -- Noncompliant
-- ...
END
Compliant solution
CREATE PROCEDURE myProc
AS
BEGIN
-- OK - Default value is ON
END
CREATE PROCEDURE myProc
AS
BEGIN
SET ANSI_WARNINGS, ARITHABORT ON;
END
