Hi!

I am between session of 24 hour of PASS right now, so I just post this piece of code I helped out with on a forum.
The original poster was amazed that

Col1 <> 'Some value'

didn't return same records as

Col1 NOT IN ('Some value')

See this example code to understand the implications of fiddling with SET ANSI_NULLS option setting.


declare @sample table
        (
            d varchar(200)
        )

Insert  @sample
select  'cancelled' union all
select  null

-- Try 1
set ansi_nulls off

select  d as [Try 1, <>, ANSI_NULLS off]
from    @sample
where   d <> 'cancelled'

select  d as [Try 1, NOT IN, ANSI_NULLS off]
from    @sample
where   d not in ('cancelled')

-- Try 2
set ansi_nulls on

select  d as [Try 2, <>, ANSI_NULLS on]
from    @sample
where   d <> 'cancelled'

select  d as [Try 2, NOT IN, ANSI_NULLS on]
from    @sample
where   d not in ('cancelled')