Q:

Why Sql Conditions (not in , not like , !=) not working on varchar value of table

I have a table with two columns and containing two records.
SQL table structure:
CREATE TABLE #TEMP_TEST
(
    ID bigint NOT NULL,
    DESCRIPTION varchar(500) NULL
) ON [PRIMARY]

INSERT INTO #TEMP_TEST VALUES(1, 'obsolete')
INSERT INTO #TEMP_TEST VALUES(2, NULL)

SELECT * FROM #TEMP_TEST

Total dummy records in #TEMP_TEST table
ID      | DESCRIPTION |
--------+-------------+
1       | obsolete    |
2       | NULL        |

Query with these conditions =, like, in are working fine.
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION ='obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION LIKE 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION IN ('obsolete')

Working fine with these results:
ID      | DESCRIPTION |
--------+-------------+
1       | obsolete    |

Problem : 
Query with these conditions !=, not like, not in are not working fine.
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION != 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION NOT LIKE 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION NOT IN ('obsolete')

Results:
ID      | DESCRIPTION |
--------+-------------+

Expected results:
ID      | DESCRIPTION |
--------+-------------+
2       | NULL        |

What should I do to get expected result?
Kindly review and give a required answer or comment if any further condition is required for getting expected result.

A:

This is because of three valued logic in SQL Server. Predicate can evaluate to:

true 
false
unknown

When you are comparing data in SQL Server you should always think about possible NULLs. Consider these predicates:
where 1=1 => evaluates to true
where 2=1 => evaluates to false
where 1=null => evaluates to unknown
where null=null => evaluates to unknown

So comparing NULL to any value, even with NULL evaluates to UNKNOWN.
Now you should know howWHEREclause works. It returns rows where predicate evaluates toTRUE` only!
In your case the predicate:
WHERE  LC2.DESCRIPTION != 'obsolete'

will evaluate to:
obselete != obselete => false
obselete != null => unknown

So there are no rows where predicate evaluates to TRUE and you get nothing as a result.
As for the question what should you do, you can do the following:
WHERE ISNULL(LC2.DESCRIPTION, 'not absolete') != 'obsolete'

but here your predicate is not SARGable and you will not gain from indexes if any is created for LC2.DESCRIPTION column.
The standard way is to use OR:
WHERE LC2.DESCRIPTION != 'obsolete' OR LC2.DESCRIPTION IS NULL 

A:

You can use OR and It will work even if ANSI_NULLS is ON
SELECT * FROM #TEMP_TEST LC2 WHERE  LC2.DESCRIPTION != 'obsolete' OR LC2.DESCRIPTION IS NULL
SELECT * FROM #TEMP_TEST LC2 WHERE  LC2.DESCRIPTION NOT LIKE 'obsolete' OR LC2.DESCRIPTION IS NULL
SELECT * FROM #TEMP_TEST LC2 WHERE  LC2.DESCRIPTION NOT IN ('obsolete') OR LC2.DESCRIPTION IS NULL

