SET ANSI_NULLS OFF

SET ANSI_NULLS OFF

by JBrooks 3. June 2009 09:10

We found an issue with some of the older scripts having SET ANSI_NULLS OFF and this causes problems.  The ANSI_NULLS ON setting is a database option that determines how NULL comparisons are handled. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.


So if I have:

        Select *
        from A
        inner join B
        on A.name = B.name

With ANSI_NULLS OFF every rows where NAME is null in A will match every row in B where NAME is null.   I can’t see when you would ever want this.  And SET ANSI_NULLS OFF in SQL 2008 and after will actually throw an error.

The second part of this problem is that for stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect.   

The third part of this problem is that when we combine our sql scripts into 1 big text file and if one of the scripts has SET ANSI_NULLS OFF then every subsequent stored proc and SQL statement is affected if it doesn’t define this setting itself.


So going forward I'm going to make sure my store proc scripts start with:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


This is generated for you automatically when you right click a stored proc and select “Modify” or “Script Stored Proc As…”

Tags:

Development | SQL