Post reply

ANSI_NULLS

  • December 4, 2003 at 3:22 pm

    #83502

    I was writing a script to find discrepancies between 2 tables with identical schemas. I wasn't finding all the differences I expected, and realised I had forgotten to test for nulls.

    As there are a lot of columns, rather than specifically test for nulls, or use COALESCE in the comparison, I decided to use SET ANSI_NULLS OFF.

    I'm obviously misunderstanding the use of the ANSI_NULLS set option, but can't find a definitive explanation.

    To quote BOL, when ANSI_NULLS is set to OFF:-

    quote:

    Also, a comparison of two expressions that have both evaluated to null values yields TRUE

    If I create a couple of tables and populate them with 2 records each:-

    create table N1(id int not null,name varchar(255) null)

    create table N2(id int not null,name varchar(255) null)

    insert N1(id,name) values (1,'SMITH')

    insert N1(id,name) values (2,null)

    insert N2(id,name) values (1,'SMITH')

    insert N2(id,name) values (2,null)

    Even when using ANSI_NULLS OFF, the following SELECT only returns the row for 'SMITH':-

    set ansi_nulls off

    select * from n1 inner join n2 on n1.id = n2.id

    where n1.name = n2.name

    And the following select returns nothing:-

    select * from n1 inner join n2 on n1.id = n2.id

    where n1.name <> n2.name

    This is the behaviour expected when ANSI_NULLS is ON.

    However, running a similar test using variables, gives different results:-

    declare @n1 varchar(255)

    declare @n2 varchar(255)

    set ansi_nulls off

    if @n1=@n2 print 'equal'

    if @n1<>@n2 print 'not equal'

    This prints 'equal', as the 2 null values compare.

    Why is the behavious different when comparing columns in a select statement, as oppose to comparing variables?

    BTW: SQL 2000 SP3, WINDOWS 2000 SP4

  • December 6, 2003 at 12:35 pm

    #484998

    quote:

    Why is the behavious different when comparing columns in a select statement, as oppose to comparing variables?

    Because it is. Documented for earlier versions, and kept this way in SQL Server 2000, no doubt, for compatibility (although it is filed under "Problems"...)

    --Jonathan



    --Jonathan

  • December 6, 2003 at 2:31 pm

    #485002

    Thanks Jonathan

    The comparison tables in the link you supplied contain the 'definitive explanation' I was searching for.

Viewing 3 posts - 1 through 2 (of 2 total)

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