Hello people,
Good night! All right ?
In this post I will comment on a resource used by many developers to quickly “solve” some common problems in the development of Transact-SQL queries. This feature is SET ANSI_WARNINGS OFF.
What is the SET ANSI_WARNINGS OFF command for?
The ANSI_WARNINGS session control option changes the behavior of the SQL Server engine for some conditions, which are:
- String Truncation (Try to store 10 character text in a VARCHAR (9))
- Division by ZERO
- NULL values in aggregate functions (MAX, SUM, COUNT, etc)
The default value is ON, which will display the ISO default behavior for this type of situation, causing error messages to be generated in the first two situations and an alert to be generated in the last situation.
If you use the SET ANSI_WARNINGS OFF command, the SQL Server engine will reproduce non-standard behavior and attempt to execute the SELECT 10 / 0 command, instead of an error message, will return NULL and display only an alert, allowing your routines return wrong information and incorrect data.
For this reason, some SQL developers, wanting to prevent the application from returning an error in these situations, end up opting for the easiest way to “solve” errors like the ones listed above instead of actually acting on the problem and solving it effectively. This usually occurs in companies due to several reasons, which I highlight:
- Lack of technical knowledge to identify and correct error
- Unwillingness to effectively solve the problem
- Very tight deadlines for smooth and error-free software delivery
Msg 8134 Divide by zero error encountered
Common error in everyday SQL developers, the classic zero-division message is a clue that some value in their calculations is wrong. This is a basic arithmetic error that causes a severe exception in SQL Server, causing the command to stop and the transaction to roll over automatically. This is the default behavior of the SQL Server engine, following the ISO standard.
Control of this behavior is done in conjunction with the ARITHABORT command. When ANSI_WARNIGS is enabled, ARITHABORT is automatically activated, making the engine run in its default mode (ISO).
When ANSI_WARNINGS is off, ARITHABORT can be changed to change the default engine behavior. If ARITHABORT is disabled, upon encountering a division by zero error, SQL Server will ignore the error and continue execution normally, only displaying an alert in the Messages tab.
Example:
|
1 2 3 4 5 6 7 8 9 |
-- Ativa o ARITHABORT automaticamente SET ANSI_WARNINGS ON -- Mesmo desativando manualmente, ficará ATIVADO por causa do ANSI_WARNINGS ON SET ARITHABORT OFF IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON' SELECT 1/0 |
As we saw in the example above, ANSI_WARNINGS OFF allows zero division accounts to run silently, generating incorrect data without the development team knowing, as no exception will be thrown in the application.
Return with SET ANSI_WARNINGS ON (Default):
Return with SET ANSI_WARNINGS OFF:
Msg 8152 String or binary data would be truncated
This error message is very common among SQL developers and surely you may have seen it while creating your queries, integrating systems, etc. This message occurs when you try to store more characters than is allowed in a column.
The default behavior of the SQL Server engine, following the ANSI standard, causes an exception when executing your Transact-SQL code if you
have a text of 10 characters and try entering that text in a column that only allows up to 9 characters.
When you use the SET ANSI_WARNINGS OFF command, you cause the SQL Server engine to no longer generate this error on execution, causing your 10 character text to be truncated and stored in the 9 character column. Excess characters will be silently discarded, ignoring and masking a problem writing your system data without anyone knowing.
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SET NOCOUNT ON SET ANSI_WARNINGS OFF IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON' IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste CREATE TABLE #Teste ( Nome VARCHAR(10) ) INSERT INTO #Teste VALUES ('Dirceu Resende') -- 14 caracteres SELECT * FROM #Teste |
Return with SET ANSI_WARNINGS ON (Default):
Return with SET ANSI_WARNINGS OFF:
Null value is eliminated by an aggregate or other SET operation
Alert message that occurs when an aggregate function (MAX, SUM, COUNT, AVG, etc.) is applied to a dataset and at least 1 record has a null value (NULL).
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SET NOCOUNT ON SET ANSI_WARNINGS ON IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON' SELECT MAX(Nota) FROM ( SELECT 7 AS Nota UNION SELECT 6.5 UNION SELECT 5.9 UNION SELECT 4.2 UNION SELECT NULL UNION SELECT 8.7 ) AS Aluno |
Return with SET ANSI_WARNINGS ON (Default):
Return with SET ANSI_WARNINGS OFF
Note that in this case, if the ANSI_WARNINGS option is disabled, you may be ignoring the null values in your result set, which may indicate a potential problem, as in certain situations this may represent an error in your query. and that you're masking.
If you want to know how to remove the warning “Null value is eliminated by an aggregate or other SET operation” in the correct way, learn more by accessing the post SQL Server - Warning: Null value is eliminated by an aggregate or other SET operation.
INSERT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'
Another serious side effect of using ANSI_WARNINGS OFF is the impact this command has on indexed views and indexes created on calculated columns, which are often designed for performance gain, but that simply creating an index to improve , ends up creating a big headache for DBA, since all routines that insert data into tables that have calculated columns will fail after the index is created on the calculated column.
Unlike previous errors, which can cause inaccurate and inconsistent data, this example prevents routines from working at all, including me, that I created an index to optimize a slow query in production and started triggering error alerts on a of the system modules where I work.
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SET ANSI_WARNINGS ON IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON' IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste CREATE TABLE #Teste ( Nota1 NUMERIC(5, 2) NOT NULL, Nota2 NUMERIC(5, 2) NOT NULL, Media AS ((Nota1 + Nota2) / 2) ) CREATE NONCLUSTERED INDEX SK01_Teste ON #Teste(Media) INSERT INTO #Teste VALUES (7.6, 8.9) SELECT * FROM #Teste |
Return with SET ANSI_WARNINGS ON (Default):
Return with SET ANSI_WARNINGS OFF:
Msg 1934, Level 16, State 1, Line 12
CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and / or indexes on computed columns and / or filtered indexes and / or query notifications and / or XML data type methods and / or spatial index operations.
That's it folks!
I hope you enjoyed the post and see you next time.
