ms sql query against null values - ansi_nulls is ON

We use the below query to check against null values
select * from table_name where column_name is null which returns the records whose values are null for that particular column.

But, the below query will not yield any result eventhough there are records which contains null values.select * from table_name where column_name = null 

In the same way
select * from table_name where column_name != null will also produce no results.

This is because, ansi_nulls is set to ON by default in SQL.To make the above query work use set ansi_nulls offNow we get the same results with the following queries  

select * from table_name where column_name = null and
select * from table_name where column_name != null