SET ANSI_NULLS ON/OFF in SQL Server

SET ANSI_NULLS ON

SELECT * FROM #Demo WHERE LName = NULL

SELECT * FROM #Demo WHERE LName <> NULL

The above query returns No result just because of the ANSI_NULL was ON.

In such a case we use IS NULL or IS NOT NULL.

SET ANSI_NULLS ON

SELECT * FROM #Demo WHERE LName IS NULL

SELECT * FROM #Demo WHERE LName IS NOT NULL

The Above query returns the rows with NULL/NOT NULL value.

When this setting value is ON then we need to use IS NULL or IS NOT NULL instead of the comparison operator = and <>.

SET ANSI_NULL OFF;

On the other hand if this setting value is OFF then the comparison with the NULL value using = and <> comparison operator returns TRUE.

SET ANSI_NULLS OFF

SELECT * FROM #Demo WHERE LName = NULL

SELECT * FROM #Demo WHERE LName <> NULL

If there is any mistake in the concept mentioned above. Then do Comment.