SQLServer "IS NULL", "IsNull()" and "= null"

Today, I ran into something very interesting.. well.. to me at least.

After coding a lot with C#, the NOT EQUAL "!=" syntax is pretty much embedded into my dairy operation in my brain.

Anyhow, surprised to me, when I run the following:

select * from someTable where someKey != null;

is not the same as:

select * from someTable  where someKey IS NOT NULL;

It turns out that I forgot using != performs a comparison operation instead of checking if the value is null. Hence, it try to test if someKey contain a value that matches the value that represent the database "null".

The comparison operation returns UNKOWNOW if either of both operands is null.

Moreover, after digging more about this issue, if you use UDT (User Define Type) in SQL Server, you can also use columnName.IsNull property to check if the column value IS NULL or not. I am not an expert of UDT, so I will stop talking about it here. Just something to know.

References:
IS [NOT] NULL (T-SQL)
MSDN Blogs on SQL Server IS NULL vs. IsNull