Understanding SET QUOTED_IDENTIFIER ON/OFF and ANSI_NULLS ON/OFF in SQL Server

The most commonly used SET options in SQL Server are QUOTED_IDENTIFIER and ANSI_NULLS. We might have seen it is used in many Store procedures, triggers and user defined functions. So, in this article, we will find out what are they and why are they important than other set options.

SET QUOTED_IDENTIFIER ON/OFF

It will specify how the SQL Server should treat the data mentioned in single and double quotes. If it is set to ON,

  • Double quoted (“”) data are referred as Identifier. For an instance, stored procedure name, trigger name, user defined name or table name. And T-SQL rules for naming identifier will be ignored (Reserved keywords)
  • Single quoted (”) data are referred as literal.
Example

QuotedIndentifier

In the above example, even though “SELECT” and “Table” are reserved keywords in SQL Server, as the QUOTED_IDENTIFIER is turned on and the data is specified in double quotes, SQL Server will ignore the naming identifier rules and accept and create a table name as “SELECT” with a column “Table”.

QuotedIndentifier_2

In the above example, SQL Server will interpret “RandomText” as a column name but it is not valid.

Let us try the same example with the setting turned OFF.QuotedIndentifier_3

In the first statement, “SELECT”  and “TABLE” are treated as a reserved keyword, so it wont let it to create a table.

In the second statement, “RandomText” is treated as a text not as a column, it will return data.

Note: By default, SE QUOTED_IDENTIFIER is ON

SET ANSI NULLS ON/OFF

This set option will specify how SQL Server will perform comparison operation will NULL values.

  • If it is ON,  SQL Server wont compare NULL value with =, <> operators and it will return always false for it. It should compare with only IS/ IS NOT operator.
    • It is ISO defined standard behaviour.
  • If it is OFF, SQL Server will compare with =, <>  operators as well.
Example

AnsiNull_2

In the above example, it returns true because, when it turned , IS/IS NOT is valid comparing operator for NULL.

AnsiNull_1

As SQL Server wont consider =,  it will return false.

Let us how does it behave when ANSI_NULLS  is turned off.

AnsiNull_3

When it is turned OFF, it will consider comparing with =, <> . Hence it returns “Same” in both cases.

Note: By default, SET ANSI_NULLS is ON

Why are these two options are special?

  • Whenever, the stored procedure, function or trigger is executed, these two options are set explicitly and SQL Server will remember the settings in associated metadata.So every time,  if it is executed,  SQL server will read from the stored settings irrespective of the current session setting. So, behaviour of the called object will remain same in any session.

The following SQL query helps to find these settings that have been used for the stored procedure/trigger/function.

StoredProcedure_Settings

 

Hope this article provides the better understanding of QUOTED_IDENTIFIER and ANSI_NULLS set options and its importance.

Happy SQL’ing 🙂

 

One thought on “Understanding SET QUOTED_IDENTIFIER ON/OFF and ANSI_NULLS ON/OFF in SQL Server”

  1. Nice post. I be taught something more challenging on totally different blogs everyday. It should at all times be stimulating to learn content from other writers and apply just a little one thing from their store. I’d desire to make use of some with the content material on my blog whether or not you don’t mind. Natually I’ll give you a hyperlink on your web blog. Thanks for sharing.

    Like

    Reply

Leave a comment