ANSI_NULLS and QUOTED_IDENTIFIER are among the most common settings that a DBA/developer may come across in Ansi Null SQL Server. Let’s have a look at these settings in detail.

ANSI_NULLS

This is used to govern NULL comparison. When ON, ISO NULL comparison standard are followed and = and <> operators aren’t used for NULL comparison instead IS NULL and IS NOT NULL are used for NULL comparison. This value is ON by default. Let’s have a look at an example.

The above query sets ANSI_NULLS = ON and creates a table with some dummy values. Let’s do NULL comparison and analyze the results.

1_ANSI_NULLS and QUOTED_IDENTIFIER in SQL Server

As explained above, the = and <> operators didn’t return any rows whereas IS NULL and IS NOT NULL returned correct results. Let’s now switch ANSI_NULLS to OFF and analyze the resultset.

2_ANSI_NULLS and QUOTED_IDENTIFIER in SQL Server

As expected, the = and <> operator return correct result with ANSI_ NULLS OFF.

In future SQL Versions, the ANSI_NULLS will always be ON  by default and the setting it to OFF will generate errors.

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook