NULL COMPARISONS V6.5 VS. 7.0
With the release of SQL Server 7.0, Microsoft put in motion the ANSI 92 
or SQL-92 standards for handling NULL comparison values. Based on the 
following statements, you will see the subtle differences in the way 
v6.5 vs. v7.0 handles the NULL search argument. SQL-92 standards 
dictate that = (equal) or <> (not equal) comparisons evaluate to false. 
This standard was not in place in v6.5.

v6.5 
SELECT * FROM TableX WHERE Col2 = NULL

This syntax was considered correct in this version and would actually 
return all the rows where Col2 is equal to NULL. This statement is now 
considered incorrect in v7.0, as it does not adhere to the SQL-92 
standards. It would evaluate to false.

v7.0  
SELECT * FROM TableX WHERE Col2 IS NULL

The IS clause replaces the = (equal) sign in this statement and 
conforms to the ANSI standard.

You can override the behavior of v7.0 with the SET ANSI_NULLS ON|OFF 
statement. When SET ANSI_NULLS is OFF, SQL Server v7.0 will evaluate 
the statement as it did in v6.5

v7.0  
SET ANSI_NULLS OFF 
SELECT * FROM TableX WHERE Col2 IS NULL

These statements mimic the behavior of v6.5. Read more about NULLS and 
database compatibility levels in MSDN Books Online.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/getstart_4fht.asp

------------------------------------------