SET ANSI_WARNINGS
Specifies SQL-92 standard behavior for several error conditions.
Syntax
SET ANSI_WARNINGS { ON | OFF }
Remarks
SET ANSI_WARNINGS affects these conditions:
- When 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 OFF, no warning is issued.
- When ON, divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When OFF, 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 attempted 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 SQL-92 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 happens in any conversion to or from binary or varbinary data, no warning or error is issued, regardless of SET options.
The user options option of sp_configure can be used to set the default setting for ANSI_WARNINGS for all connections to the server. For more information, see sp_configure or Setting Configuration Options.
SET ANSI_WARNINGS must be ON when 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 Using SET Statements in SET.
Microsoft® SQL Server™ includes the ANSI warnings database option, which 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 uses the ANSI warnings setting of sp_dboption. For more information, see sp_dboption or Setting Database Options.
ANSI_WARNINGS should be set to ON for executing distributed queries.
The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_WARNINGS to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties set in the application before connecting. SET ANSI_WARNINGS defaults to 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.
Permissions
SET ANSI_WARNINGS permissions default to all users.
Examples
This example demonstrates the three situations mentioned above with the SET ANSI_WARNINGS to ON and OFF.
USE pubs
GO
CREATE TABLE T1 ( a int, b int NULL, c varchar(20) )
GO
SET NOCOUNT ON
GO
INSERT INTO T1 VALUES (1, NULL, '')
INSERT INTO T1 VALUES (1, 0, '')
INSERT INTO T1 VALUES (2, 1, '')
INSERT INTO T1 VALUES (2, 2, '')
GO
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 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
PRINT 'Testing Divide by zero'
GO
SELECT a/b FROM T1
GO
DROP TABLE T1
GO