In ASE the setting ‘Set ANSI_NULLS OFF’ sometimes is not respected. In MS SQL Server, the setting is always respected.

Example

set ANSINULL OFF -- set ANSI_NULLS OFF

create table Example (ID int, Description varchar(30))

insert into Example values (1, 'fridge')

declare @Description varchar(30)

set @Description = 'fridge'

if @Description <> (select Description from Example where ID = 42)

print 'No match found'

else

print 'They are the same'

drop table Example

Note: Because there is no record with ID = 42, the query returns NULL. Because the ANSINULL setting is OFF, a comparison with NULL should not automatically yield NULL. The value ‘fridge’ does not equal NULL, so the expected answer is ‘No match found’. If the comparison operator ‘<>’ in the ‘If’ statement is changed into ‘=’, ASE keeps choosing the ‘else’ branch. This behavior is consistent with ‘Set ANSINULL ON’, instead of with ‘Set ANSINULL OFF’.