SQL SERVER: What is QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF

In this post, I provide a small note on QUOTED_IDENTIFIER and ANSI_NULL of SQL Server.
This is very basic and most popular topic for all SQL Server Professionals.
You can find an N number of article and discussion on this topic. But I always like to write this kind of popular topic in my words.

QUOTED_IDENTIFIER ON/OFF:

This option is ON and characters enclosed within double quotes than is treated as an Identifier.
This option is OFF and characters enclosed within double quotes than is treated as a Literal.
Using this option you can use SQL Server reserved words as an Identifires.
For example:

ANSI_NULL ON/OFF:

This option basically for ANSI NULL comparisons. If this option ON and you compare your query value with a NULL, then result is 0.
If this option OFF and you compare your query value with a NULL, then result is NULL.

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz