Transact-SQL Syntax ConventionsSpecifies ISO standard behavior for several error conditions.
Transact-SQL Syntax Conventions
-- Syntax for SQL Server and Azure SQL Database
SET ANSI_WARNINGS { ON | OFF }-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
SET ANSI_WARNINGS ONSET ANSI_WARNINGS affects the following conditions:
When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued.
When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned. The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the ISO standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.
[!NOTE]
When truncation occurs in any conversion to or from binary or varbinary data, no warning or error is issued, regardless of SET options.
[!NOTE]
ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.
You can use the user options option of sp_configure to set the default setting for ANSI_WARNINGS for all connections to the server. For more information, see sp_configure (Transact-SQL).
SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS 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 You Use the SET Statements” in SET Statements (Transact-SQL).
SQL Server includes the ANSI_WARNINGS database option. This is equivalent to SET ANSI_WARNINGS. When SET ANSI_WARNINGS is ON, errors or warnings are raised in divide-by-zero, string too large for database column, and other similar errors. When SET ANSI_WARNINGS is OFF, these errors and warnings are not raised. The default value in the model database for SET ANSI_WARNINGS is OFF. If not specified, the setting of ANSI_WARNINGS applies. If SET ANSI_WARNINGS is OFF, SQL Server SQL Server uses the value of the is_ansi_warnings_on column in the sys.databases catalog view.
ANSI_WARNINGS should be set to ON for executing distributed queries.
The SQL Server Native Client ODBC driver and SQL Server SQL Server Native Client OLE DB Provider for SQL Server SQL Server SQL Server automatically set ANSI_WARNINGS to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, set in the application before connecting. The default for SET ANSI_WARNINGS is OFF for connections from DB-Library applications.
When SET ANSI_DEFAULTS is ON, SET ANSI_WARNINGS is enabled.
The setting of SET ANSI_WARNINGS is set at execute or run time and not at parse time.
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.
To view the current setting for this setting, run the following query.
DECLARE @ANSI_WARN VARCHAR(3) = 'OFF';
IF ( (8 & @@OPTIONS) = 8 ) SET @ANSI_WARN = 'ON';
SELECT @ANSI_WARN AS ANSI_WARNINGS; Requires membership in the public role.
The following example demonstrates the three situations that are previously mentioned, with the SET ANSI_WARNINGS to ON and OFF.
USE AdventureWorks2012;
GO
CREATE TABLE T1
(
a int,
b int NULL,
c varchar(20)
);
GO
SET NOCOUNT ON;
INSERT INTO T1
VALUES (1, NULL, '')
,(1, 0, '')
,(2, 1, '')
,(2, 2, '');
SET NOCOUNT OFF;
GO
PRINT '**** Setting ANSI_WARNINGS ON';
GO
SET ANSI_WARNINGS ON;
GO
PRINT 'Testing NULL in aggregate';
GO
SELECT a, SUM(b)
FROM T1
GROUP BY a;
GO
PRINT 'Testing String Overflow in INSERT';
GO
INSERT INTO T1
VALUES (3, 3, 'Text string longer than 20 characters');
GO
PRINT 'Testing Divide by zero';
GO
SELECT a / b AS ab
FROM T1;
GO
PRINT '**** Setting ANSI_WARNINGS OFF';
GO
SET ANSI_WARNINGS OFF;
GO
PRINT 'Testing NULL in aggregate';
GO
SELECT a, SUM(b)
FROM T1
GROUP BY a;
GO
PRINT 'Testing String Overflow in INSERT';
GO
INSERT INTO T1
VALUES (4, 4, 'Text string longer than 20 characters');
GO
SELECT a, b, c
FROM T1
WHERE a = 4;
GO
PRINT 'Testing Divide by zero';
GO
SELECT a / b AS ab
FROM T1;
GO
DROP TABLE T1 INSERT (Transact-SQL)
SELECT (Transact-SQL)
SET Statements (Transact-SQL)
SET ANSI_DEFAULTS (Transact-SQL)
SESSIONPROPERTY (Transact-SQL)