This is a follow up blog post of SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation. I wrote that blog six years ago and I had plans that I will write a follow up blog post of the same. Today, when I was going over my to-do list and I was surprised that I had an item there which was six years old and I never got to do that.
In the earlier blog post I wrote about exploitation of the Quoted Identifier and ANSI Null. In this blog post we will see a quick example of Quoted Identifier. However, before we continue this blog post, let us see a refresh what both of Quoted Identifider do.
QUOTED IDENTIFIER ON/OFF
This option specifies the setting for use of double quotes. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.
In simple words when we have QUOTED IDENTIFIER ON, anything which is wrapped in double quotes becomes an object.
E.g.
-- The following will work
SET QUOTED_IDENTIFIER ON
GO
CREATE DATABASE "Test1"
GO
-- The following will throw an error about Incorrect syntax near 'Test2'.
SET QUOTED_IDENTIFIER OFF
GO
CREATE DATABASE "Test2"
GO
This feature is particularly helpful when we are working with reserved keywords in SQL Server. For example if you have to create a database with the name VARCHAR or INT or DATABASE you may want to put double quotes around your database name and turn on quoted identifiers to create a database with the such name. Personally, I do not think so anybody will ever create a database with the reserve keywords intentionally, as it will just lead to confusion.
Here is another example to give you further clarity about how Quoted Idenifier setting works with SELECT statement.
-- The following will throw an error about Invalid column name 'Column'.
SET QUOTED_IDENTIFIER ON
GO
SELECT "Column"
GO
-- The following will work
SET QUOTED_IDENTIFIER OFF
GO
SELECT "Column"
GO
Personally,
I always use the following method to create database as it works irrespective of what is the quoted identifier’s status. It always creates objects with my desire name whenever I would like to create.
CREATE DATABASE [Test3]
I believe the future of the quoted identifier on or off is useful in the real world when we have script generated from another database where this setting was ON and we have to now execute the same script again in our environment again.
Question to you –
I personally have never used this feature as I mentioned earlier. I believe this feature is there to support the scripts which are generated in another SQL Database or generate the script for other database. Do you have a real world scenario where we need to turn on or off Quoted Identifiers.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Yes, I have real word scenario. The situation is like this – We have to add a computed column and index on that. To work this, you must have to have the quoted identifier on.
Also if your procedures that are going to do insert/updare on that table and those prorcedures are created with quoted identifier OFF then so you must have to re-create procedures with the quoted identifier ON other wise it will lead to below error.
Msg 1934, Level 16, State 1, Procedure XXXX
INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Yes, I have a real world scenario as well. I got the same error as mentioned by vinayak.
The Query was working fine with a cursor to retrieve rows and send email feature in it. But when I used the same query in SQLJOB, I got this error.
Here is the code snippet
SET @tableHTML =
N’ANCDS Application’ +
N’Following Bulletins need attention’ +
N’Click Dashboard to open the application or individual links below to take quick action. ‘+
N” +
N’Bulletin NumberBulletin Name’ +
CAST ( ( SELECT DISTINCT
td = NavigateURL , ”
FROM [dbo].[vwDailySummary]
WHERE Status = 2 AND EmailId = @EmailID
FOR XML PATH(‘tr’), TYPE
).value(‘.’,’NVARCHAR(MAX)’) AS NVARCHAR(MAX) ) +
N” ;
Get this message while attaching a database to SQL 2008 R2.
Msg 1934, Level 16, State 1, Procedure DDL_RESTRICT_DB_FILE_PLACEMENT, Line 90
SELECT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
how to fix this?
Imagine a table with an XML column (called ‘Data’) where you need to run an XPATH query on that XML column to look for some value so that you can delete that row.
Without setting quoted identifier the following statement will fail.
SET QUOTED_IDENTIFIER ON
DELETE T
FROM table T with (NOLOCK)
WHERE Data.query(‘/X/Y[@name = “Z”]’).value(‘.’, ‘varchar(max)’) = ‘Some Value’
The scenario i faced
When we disable all Constraints & triggers and try to clean database with
–Delete
EXEC sp_MSForEachTable ‘SET QUOTED_IDENTIFIER ON DELETE FROM ?’
If any table has “XML Column” it will throw exception and wont delete them untill Quoted_Identified Is Turned ON
We faced an issue when we tried to use indexed views to help with DB performance. Indexed Views require ‘SET QUOTED_IDENTIFIER ON’
Some of our stored procedures were created in an environment with this set to off. We had to drop and recreate those procs after changing the setting to on.
I find REAL useful identify off when you have to create an SQL string for exec
I think Gulshan posted a smilar snippet. BTW…
// Snippet
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @cSQL AS NVARCHAR(MAX)
SET @cSQL=” SELECT * FROM myTable WHERE COUNTRY=’IT’ AND CITY=’ROME’ “;
exec @cSQL
it would works and I didnt will be crazy with double single quote. Image this one in a complex SELECT
hope it will help
Just hit a real world issue after implementing a persisted calculated column in a reporting table. The reporting table is a child table with a cascade delete clause. When the parent record was deleted in a cleanup stored procedure it would fail with an error that was fixed by adding “SET QUOTED_IDENTIFIER ON” to the stored procedure.
Error:
DELETE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Please help I am getting this error when I am trying to delete a table
Msg 1934, Level 16, State 1, Procedure DBA_Audit, Line 11 [Batch Start Line 2]
SELECT failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
— HERE IS REAL WORLD SCENARIO EXAMPLE — use this while using string functions with single quotes all over
SET QUOTED_IDENTIFIER off
select LEN(“insert into @PadFields values(’empID’,’EmployeeName’)”)
C:UsersadminXXX>sqlcmd -E -S XXXXXXXXXX -d DBA -q select * from vw_test1
Msg 1934, Level 16, State 1, Server XXXXXXXXXX, Line 1
SELECT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
1> exit
The above problem was resolved when I added the “SET QUOTED_IDENTIFIER ON” in the following way :
C:Usersadminappgupta>sqlcmd -E -S DRPRDNAVCL03
1> use DBA
2> go
Changed database context to ‘DBA’.
1> SET QUOTED_IDENTIFIER ON
2> go
1> select * from vw_test1
2> go
Login_Name Login_Type Login_SID AD_SID Role ID Time_Stamp