Post reply

What is the impact of setting SET ANSI_WARNINGS OFF?

  • August 24, 2016 at 5:54 am

    #1897145

    It is not true that ANSI WARNINGS OFF generates a result of NULL on zero divide.

    Moreover - there is no result generated at all.

    What happens is that after a zero divide SQL Server cancels the assignment of the value,

    and ARITHABORT OFF decides, whether execution continues after the zero devide or not.

    To see this, try the following

    set arithabort off

    set ansi_warnings on

    declare @i int = 1 / 0

    declare @j-2 int = 1

    set @j-2 = 1/0

    select @i , @j-2 --> yields @i = NULL, @j-2 = 1

  • August 24, 2016 at 9:01 am

    #1897226

    h.tobisch (8/24/2016)

    It is not true that ANSI WARNINGS OFF generates a result of NULL on zero divide.

    Moreover - there is no result generated at all.

    What happens is that after a zero divide SQL Server cancels the assignment of the value,

    and ARITHABORT OFF decides, whether execution continues after the zero devide or not.

    To see this, try the following

    set arithabort off

    set ansi_warnings on

    declare @i int = 1 / 0

    declare @j-2 int = 1

    set @j-2 = 1/0

    select @i , @j-2 --> yields @i = NULL, @j-2 = 1

    If ANSI_WARNINGS is ON and ARITHABORT is OFF, the SELECT after the divide by zero does work however an error continues to be reported in the "Messages" tab of SSMS (and to calling applications). If ANSI_WARNINGS are OFF, no error is reported (the "Messages" tab of SSMS will simply say "Command completed successfully.").

    Hence, when ANSI_WARNINGS are OFF, the calling applications never realize that something bad happened (a potentially bad data condition arose and was processed) - which can create issues depending upon the business/application logic.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • August 24, 2016 at 9:47 am

    #1897232

    h.tobisch (8/24/2016)

    It is not true that ANSI WARNINGS OFF generates a result of NULL on zero divide.

    Moreover - there is no result generated at all.

    What happens is that after a zero divide SQL Server cancels the assignment of the value,

    and ARITHABORT OFF decides, whether execution continues after the zero devide or not.

    To see this, try the following

    set arithabort off

    set ansi_warnings on

    declare @i int = 1 / 0

    declare @j-2 int = 1

    set @j-2 = 1/0

    select @i , @j-2 --> yields @i = NULL, @j-2 = 1

    That test isn't showing what you think it is showing. The issue is that, as of SQL Server 2005, setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON; you cannot have ARITHABORT OFF when ANSI_WARNINGS is ON. The following tests show the actual behavior across all 4 (technically 3) combinations:

    PRINT '---------- BOTH OFF ----------------';

    SET ANSI_WARNINGS OFF;

    SET ARITHABORT OFF;

    SELECT 'A' AS [A], 1 / 0 AS 'Error';

    SELECT 'B' AS , 1 / 0 AS 'Error';

    -- 2 result sets, each with a NULL in the "Error" field

    GO

    PRINT '-------- WARN ON, ABORT OFF --------';

    SET ANSI_WARNINGS ON;

    SET ARITHABORT OFF;

    SELECT 'C' AS [C], 1 / 0 AS 'Error';

    SELECT 'D' AS [D], 1 / 0 AS 'Error';

    -- 2 errors reported + 2 result sets, each having no rows

    GO

    PRINT '-------- WARN OFF, ABORT ON --------';

    SET ANSI_WARNINGS OFF;

    SET ARITHABORT ON;

    SELECT 'E' AS [E], 1 / 0 AS 'Error';

    SELECT 'F' AS [F], 1 / 0 AS 'Error';

    -- 1 error reported + 1 result set having no rows

    GO

    PRINT '---------- BOTH ON ----------------';

    SET ANSI_WARNINGS ON; -- automatically turns ARITHABORT to ON as of SQL Server 2005

    SET ARITHABORT ON; -- not necessary; here for clarity

    SELECT 'G' AS [G], 1 / 0 AS 'Error';

    SELECT 'H' AS [H], 1 / 0 AS 'Error';

    -- 2 errors + 2 result sets, each having no rows (same as WARN ON, ABORT OFF test)

    GO

    PRINT '--------------------------';

    The MSDN page for SET ARITHABORT states:

    If SET ARITHABORT is ON and SET ANSI WARNINGS is ON, these error conditions cause the query to terminate.

    If SET ARITHABORT is ON and SET ANSI WARNINGS is OFF, these error conditions cause the batch to terminate. If the errors occur in a transaction, the transaction is rolled back. If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation.

    If SET ARITHABORT is OFF and SET ANSI WARNINGS is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation.

    Take care,

    Solomon...

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • August 24, 2016 at 9:51 am

    #1897235

    Solomon Rutzky (8/23/2016)

    N_Muller (8/23/2016)

    SQL Server doesn't allow to create filtered indexes or indexes on computed columns in temp tables with ansi warnings turned off. Here's a simple example:

    More specifically, SQL Server does not allow indexes in a few scenarios when ANSI_WARNINGS is OFF, though tempdb has nothing to do with it:

    ...

    Solomon - thank you for clarifying this.

  • August 24, 2016 at 10:08 am

    #1897243

    set arithabort off

    set ansi_warnings on

    declare @i int = 1 / 0

    declare @j-2 int = 1

    set @j-2 = 1/0

    select [@i]=@i , [@j]=@j --> yields @i = NULL, @j-2 = 1

    I DID have arithabort off while having ansi_warnings on because 1 got this

    -->

    Msg 8134, Level 16, State 1, Line 4

    Divide by zero error encountered.

    Msg 8134, Level 16, State 1, Line 6

    Divide by zero error encountered.

    (1 row(s) affected)

    AND this

    -->

    @i@j-2

    NULL1

    regards Herbert

  • August 24, 2016 at 2:14 pm

    #1897323

    h.tobisch (8/24/2016)

    set arithabort off

    set ansi_warnings on

    declare @i int = 1 / 0

    declare @j-2 int = 1

    set @j-2 = 1/0

    select [@i]=@i , [@j]=@j --> yields @i = NULL, @j-2 = 1

    I DID have arithabort off while having ansi_warnings on because 1 got this

    -->

    Msg 8134, Level 16, State 1, Line 4

    Divide by zero error encountered.

    Msg 8134, Level 16, State 1, Line 6

    Divide by zero error encountered.

    (1 row(s) affected)

    AND this

    -->

    @i@j-2

    NULL1

    regards Herbert

    Ok. Now set arithabort on as that first line in your test and re-run. How does the behavior / outcome change? For me, it doesn't. The behavior is the same between arithabort ON and arithabort OFF, even though @@OPTIONS does register the difference between ON and OFF for arithabort.

    Take care,

    Solomon..

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • September 5, 2016 at 4:45 am

    #1899179

    Thank-you, all for taking the time to read my article and share your valuable feedback. I have made to the article since the original publication:

    - Added a new section – “Impact on Table Design and Index Management”

    - Added a caution note under section “How to configure ANSI_WARNINGS for all sessions on the server?”

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply