ANSI_NULLS - Equalling NULLs

********************Check For NULL********************

If you check for Null using <> or = operator, it might not return the results even if the table contains NULLs

Select * from Person.Address where AddressLine2 = Null

will not yield any results

on the otherhand it is a good practice to get the intended value using the following

Select * from Person.Address where AddressLine2 Is Null

If you still want to use = or <> with Nulls then the following will work

SET ANSI_NULLS OFF
Select * from Person.Address where AddressLine2 = Null

However, this feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.