Rajendra Gupta
SSMS execution plan

SET QUOTED_IDENTIFIER settings in SQL Server

October 17, 2019 by

In this article, we will explore QUOTED_IDENTIFIER behavior, default value, and comparison with a different value. Set options at connection level control query behavior in SQL Server. The query might behave differently with different set options and their values.

Introduction

Most of the developers use SET QUOTED_IDENTIFIER ON/OFF option in a stored procedure and user-defined functions. If you have not used it, SQL Server automatically adds this while scripting out the objects. This article explains the configuration and its benefits.

Let’s create a stored procedure with the following script:

In SSMS’s Object Explorer, right-click on the stored procedure and generate the stored procedure script (Script stored procedure as -> Create To -> New Query Editor Window):

Script stored procedure

It generates the script in the new query window of SSMS. In the generated script, we can see an option SET QUOTED_IDENTIFIER ON. We have not added any such argument while creating the stored procedure. You can review the stored procedure script again for verification:

SSMS Script

You are probably asking yourself why SSMS adds these options while generating a script for an object?

Let’s understand the QUOTED_IDENTIFIER option in SQL Server in the next section.

SET QUOTED_IDENTIFIER

We use single or double quotes in the script. Suppose we want to store employees’ name in a SQL table. The employee name might include a single quote in the name as well.

Let’s say customer name is O’Brien, if we try to insert this name in a table, we get the following error message:

Error message:

207, Level 16, State 1, Line 3
Invalid column name ‘O’Brien’.

We can execute the query as shown below, and it inserts record successfully in the table:

We can use single quotes as well to insert the data.

QUOTED_IDENTIFIER controls the behavior of SQL Server handling double-quotes.

Look at the following example with different values of QUOTED_IDENTIFIER:

Example 1: SET QUOTED_IDENTIFIER OFF

It gives the output Rajendra:

output

Example 2: SET QUOTED_IDENTIFIER ON

Let’s turn this option ON and execute the query.

We get an error message stating invalid column name:

Invalid column error

  • SET QUOTED_IDENTIFIER OFF: If this setting is off, SQL Server treats the value inside the double quotes as a string. We can use any string in the double quotes, and SQL Server does not check for rules such as reserved keyword
  • SET QUOTED_IDENTIFIER ON: With this option, SQL Server treats values inside double-quotes as an identifier. It is the default setting in SQL Server. In the above example, we see that it treats the string Rajendra. It checks for the column name and gives an error message

We cannot use a reserved keyword for object names. For example, function is a reserved keyword, and we cannot create any existing object with this:

It gives the following error message:

Incorrect syntax error

As highlighted earlier, we can use reserved keywords in the object name once we enable the QUOTED_IDENTIFIER ON and SQL Server does not check the string for any existing rules.

In the following query, we use the reserved keyword function as an object:

Once the object is created, we can access the object without specifying the QUOTED_IDENTIFIER option. Both queries execute successfully:

Output message

Let’s view the behavior of single quotes along with QUOTED_IDENTIFIER ON and OFF:

As you can see in the following screenshot, it treats string inside single quotes as literal. In the first select statement, it prints the string inside a single quote.

Output of an query

With the QUOTED_IDENTIFIER OFF, it gives a similar output. This setting does not impact SQL Server behavior for the single quotes:

Output of an query

In the following example, let’s observe a single and double quote behavior with QUOTED_IDENTIFIER:

Both Select statements return similar output once we turn off the QUOTED_IDENTIFIER option:

Output of select statement with quotes

In another example, we use previous Select statements with the QUOTE_INDENTIFIER ON:

We get an error message with the double-quotes. You can also see that SQL Server does not parse the double-quoted string. It cannot recognize the object and highlights the string with an underline:

Output of an queery with single and double quotes

Let’s look at a few complex examples with a combination of single and double quotes in a string:

It contains three single quotes marks:

  • SQL Server treats the first single quote as a string delimiter
  • It treats the second single quote as a part of the string

Multiple quotes

Multiple single quotes

Multiple single quotes error msg

If we use a double quote and single quote together, it treats double quote as string delimiter and prints the other single quote. We can explore a few more commands to see the behavior of QUOTED_IDENTIFIER:

Combination of single and double quotes

Combination of quotes and SET QUOTED_IDENTIFIER

Identify objects using QUOTED_IDENTIER explicitly

We can use sys.sql_modules to filter the results using the QUOTED_IDENTIFIER setting ON or OFF. It shows the objects in which we defined QUOTED_IDENTIFIER explicitly. It does not show the objects using the default behavior:

  • Query to find objects using QUOTED_IDENTIFIER ON

  • Query to find objects using QUOTED_IDENTIFIER OFF

SSMS property to control QUOTED_IDENTIFIER

At the beginning of the article, we created a new stored procedure without specifying QUOTED_IDENTIFIER. Later, we generated the script, and it includes SET QUOTED_IDENTIFIER ON option.

We can control this behavior using SSMS settings. In SSMS, go to Tools -> Options.

It gives various options to control SSMS behavior such as environment details, query execution, query results behavior:

SSMS options

It also has a search option in that we can filter out the required configuration. Search for keyword ANSI, and it gives you settings to control query execution behavior.

In this screenshot, we can see it has an option SET QUOTED_IDENTIFIER, and it is enabled. It is the default behavior of SQL Server:

SSMS ANSI options

If we want to turn off QUOTE_IDENIFIER, we can remove the tick from the checkbox:

Turn off SET QUOTED IDENTIFIER

Click OK and restart the SSMS so that this setting can take effect. Let’s create the procedure [dbo].[SQLTest] again and generate the script for it. In the below screenshot, we can see that it has QUOTED_IDENTIFIER OFF now:

change default bahaviour of SSMS

Note: If we change the QUOTED_IDENTIFIER option SSMS tools, it turns off the QUOTED_IDENTIFIER for all client session. You should be careful about modifying this option in the production environment.

Verify QUOTED_IDENTIFIER in the SSMS execution plan

We can use the SQL execution plan to view the current value of QUOTED_IDENTIFIER. Enable Actual execution plan before executing the query (shortcut key – CTRL+M):

We executed this query by specifying QUOTE_INDENTIFIER value OFF, let’s view the execution plan. Under the execution plan properties, we can see the Set option and its current value in the query:

SSMS execution plan

A quick summary of QUOTED_IDENTIFIER

Parameter

QUOTED_IDENTIFIER ON

QUOTED_IDENTIFIER OFF

Behaviour

Default

Not default

The string within double quotes

It treats a string as an Identifier:

It gives the following error message:

Msg 207, Level 16, State 1, Line 4
Invalid column name ‘Rajendra’.

It treats the string as literal.

This statement works fine:

Use reserved keyword

We can use a reserved keyword in the object name within double-quotes.

We cannot use the reserved keyword. We get an error message – Incorrect syntax error (102, Level 15, State 1)

Identify objects

If we define explicitly QUOTED_IDENTIFIER value, we can find it using the following query:

We can use the following query for finding objects with QUOTED_IDENTIFIER OFF:

Conclusion

This article gives an overview of the QUOTED_IDENTIFIER set option and its behavior in SQL Server queries with single and double-quotes. We should be careful while changing the default value. You should change as per requirement only.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views