SET QUOTED IDENTIFIER settings in SQL Server
SET QUOTED_IDENTIFIER settings in SQL Server
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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolSET QUOTED_IDENTIFIER settings in SQL Server
October 17, 2019 by Rajendra Gupta 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: 12345 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: 12345 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: 123456 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
1234 SET QUOTED_IDENTIFIER OFFGOSELECT "Rajendra"GO It gives the output Rajendra:Example 2 SET QUOTED_IDENTIFIER ON
Let’s turn this option ON and execute the query. 1234 SET QUOTED_IDENTIFIER ONGOSELECT "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: 12345678910111213141516 SET QUOTED_IDENTIFIER ON; GOCREATE TABLE dbo."Function"(id INT, Name VARCHAR(20));GOINSERT 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: 1234 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: 123 SET QUOTED_IDENTIFIER ON;GOSELECT '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: 123 SET QUOTED_IDENTIFIER OFFGOSELECT 'Rajendra' In the following example, let’s observe a single and double quote behavior with QUOTED_IDENTIFIER: 12345 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: 12345 SET QUOTED_IDENTIFIER ON GOSELECT 'Rajendra' GOSELECT "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: 123 SET QUOTED_IDENTIFIER OFF;GOSELECT '''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 123 SET QUOTED_IDENTIFIER ON;GOSELECT '''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: 12345678 SET QUOTED_IDENTIFIER OFF;GOSELECT "'Rajendra '''' Gupta'";SELECT 'Rajendra '''' Gupta';SELECT '"Rajendra Gupta"';SELECT """Rajendra""";SELECT "Rajendra """" Gupta";GO 12345678 SET QUOTED_IDENTIFIER ON;GOSELECT "'Rajendra '''' Gupta'";SELECT 'Rajendra '''' Gupta';SELECT '"Rajendra Gupta"';SELECT """Rajendra""";SELECT "Rajendra """" Gupta";GOIdentify 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 123 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 1 Query to find objects using QUOTED_IDENTIFIER OFF 123 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 0SSMS 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): 1234567 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: 1234 SET QUOTED_IDENTIFIER ONGOSELECT "Rajendra" 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: 123 SET QUOTED_IDENTIFIER OFFGOSELECT "Rajendra" 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: 123 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 1 We can use the following query for finding objects with QUOTED_IDENTIFIER OFF: 123 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 0Conclusion
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. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022