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:
|
1 2 3 4 5 |
CREATE PROCEDURE [dbo].[SQLTest] AS BEGIN SELECT 'Rajendra'; END; |
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):
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:
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:
|
1 2 3 4 5 |
Insert into [Employee] Values( 'Joe', "O'Brien", 'UK') |
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:
|
1 2 3 4 5 6 |
SET QUOTED_IDENTIFIER OFF Insert into [Employee] Values( 'Joe', "O'Brien", 'UK') |
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
|
1 2 3 4 |
SET QUOTED_IDENTIFIER OFF GO SELECT "Rajendra" GO |
It gives the output Rajendra:
Example 2: SET QUOTED_IDENTIFIER ON
Let’s turn this option ON and execute the query.
|
1 2 3 4 |
SET QUOTED_IDENTIFIER ON GO SELECT "Rajendra" GO |
We get an error message stating invalid column name:
- 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:
|
1 |
CREATE TABLE Function(id INT); |
It gives the following error message:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SET QUOTED_IDENTIFIER ON; GO CREATE TABLE dbo."Function" (id INT, Name VARCHAR(20) ); GO INSERT INTO dbo."Function" (id, Name ) VALUES (1, 'Rajendra' ); GO |
Once the object is created, we can access the object without specifying the QUOTED_IDENTIFIER option. Both queries execute successfully:
|
1 2 3 4 |
SELECT id,name FROM dbo."Function" SELECT [id], [Name] FROM [DemoSQL].[dbo].[Function]; |
Let’s view the behavior of single quotes along with QUOTED_IDENTIFIER ON and OFF:
|
1 2 3 |
SET QUOTED_IDENTIFIER ON; GO SELECT 'Rajendra'; |
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.
With the QUOTED_IDENTIFIER OFF, it gives a similar output. This setting does not impact SQL Server behavior for the single quotes:
|
1 2 3 |
SET QUOTED_IDENTIFIER OFF GO SELECT 'Rajendra' |
In the following example, let’s observe a single and double quote behavior with QUOTED_IDENTIFIER:
|
1 2 3 4 5 |
SET QUOTED_IDENTIFIER OFF GO SELECT 'Rajendra' GO SELECT "Rajendra" |
Both Select statements return similar output once we turn off the QUOTED_IDENTIFIER option:
In another example, we use previous Select statements with the QUOTE_INDENTIFIER ON:
|
1 2 3 4 5 |
SET QUOTED_IDENTIFIER ON GO SELECT 'Rajendra' GO SELECT "Rajendra" |
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:
Let’s look at a few complex examples with a combination of single and double quotes in a string:
|
1 2 3 |
SET QUOTED_IDENTIFIER OFF; GO SELECT '''Rajendra'''; |
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
|
1 2 3 |
SET QUOTED_IDENTIFIER ON; GO SELECT '''Rajendra'''; |
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:
|
1 2 3 4 5 6 7 8 |
SET QUOTED_IDENTIFIER OFF; GO SELECT "'Rajendra '''' Gupta'"; SELECT 'Rajendra '''' Gupta'; SELECT '"Rajendra Gupta"'; SELECT """Rajendra"""; SELECT "Rajendra """" Gupta"; GO |
|
1 2 3 4 5 6 7 8 |
SET QUOTED_IDENTIFIER ON; GO SELECT "'Rajendra '''' Gupta'"; SELECT 'Rajendra '''' Gupta'; SELECT '"Rajendra Gupta"'; SELECT """Rajendra"""; SELECT "Rajendra """" Gupta"; GO |
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
123SELECT OBJECT_NAME(object_id)FROM sys.sql_modulesWHERE uses_quoted_identifier = 1 -
Query to find objects using QUOTED_IDENTIFIER OFF
123SELECT OBJECT_NAME(object_id)FROM sys.sql_modulesWHERE uses_quoted_identifier = 0
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:
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:
If we want to turn off QUOTE_IDENIFIER, we can remove the tick from the checkbox:
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:
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):
|
1 2 3 4 5 6 7 |
SET QUOTED_IDENTIFIER OFF; GO SELECT TOP (1000) [Firstname] ,[Lastname] ,[Country] FROM [DemoSQL].[dbo].[Employee] |
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:
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023