Tag Archives: SET QUOTED_IDENTIFIER ON

INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ …

In this article we will discuss on when we get the errors like below in Sql Server and how to resolve them.

Msg 1934, Level 16, State 1, Procedure AddEmployee, Line 5

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.

First to reproduce this scenario we will create a demo db, table, populate sample data in the table and create a stored procedure to add the data to table:

CREATE DATABASE DemoSQLHints
GO
USE DemoSQLHints
GO
SET ANSI_NULLS ON
GO
CREATE TABLE dbo.Employee(EmployeeId int identity(1,1),
                   FirstName VARCHAR(50),LastName  VARCHAR(50))
GO
--Insert 1k records using GO statement as below
INSERT INTO dbo.Employee(FirstName,LastName) VALUES(NEWID(),NEWID())
GO 1000

GO
--Create Stored Procedure to insert record in Employee Table with
--QUOTED_IDENTIFIER setting set to "OFF"

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE DBO.AddEmployee(@FirstName VARCHAR(50),@LastName VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON 
 INSERT INTO dbo.Employee(FirstName,LastName)
 VALUES (@FirstName, @LastName )
END
GO

Below statement to insert record in the Employee table, successfully executes and inserts a record in the employee table:

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

Now try to create a filtered index (New Feature introduced in Sql Server 2008) on the employee table as shown below. To create filtered index, sql server requires it to be created with SET QUOTED_IDENTIFIER setting as ON.

SET QUOTED_IDENTIFIER ON
GO
CREATE NONCLUSTERED INDEX IX_Emplyoee_EmployeeId
 ON Employee(EmployeeId) WHERE EmployeeId > 500
GO

After creating the above filtered index, try executing the below statement which was executed successfully prior to the creation of the filtered index.

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

This time the execution of the above statement returns the below error:

Msg 1934, Level 16, State 1, Procedure AddEmployee, Line 5

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.

Reason for this error is:  Employee table has filtered index and due to this any DML statement on this table which is executed with SET QUOTED_IDENTIFIER setting as OFF will result in failure. Here as the stored procedure AddEmployee is created with SET QUOTED_IDENTIFIER setting as OFF, so whenever this sp is executed it will use this setting stored in the meta data.

To solve this issue, we need to re-create the SP AddEmployee  with QUOTED_IDENTIFIER setting as ON as shown below:

DROP PROCEDURE dbo.AddEmployee
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DBO.AddEmployee(@FirstName VARCHAR(50),@LastName VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON 
 INSERT INTO dbo.Employee(FirstName,LastName)
 VALUES (@FirstName, @LastName )
END
GO

Now, try executing the below statement:

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

Now, the above statement executes successfully and inserts a record in the employee table 🙂

You would also like to gothrough the article SET Options with their setting values required while working with filtered index

Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. It is better practice to use SET QUOTED_IDENTIFIERS ON setting.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version

[ALSO READ] Difference Between SET QUOTED_IDENTIFIER ON and OFF setting

Difference Between SET QUOTED_IDENTIFIER ON and OFF setting in SQL Server

In this article we will discuss on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF. Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. As a better practice we should always use SET QUOTED_IDENTIFIERS ON setting.

SET QUOTED_IDENTIFIER ON SET QUOTED_IDENTIFIER OFF
Characters Enclosed within double quotes is treated as Identifier is treated as Literal
Try using Characters Enclosed within double quotes as identifier Works
Example: Below statement to create a table with table name “Table” succeeds.

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo."Table"
(id int,
"Function" VARCHAR(20)) 
GO
Fails
Example: Below statement to create a table with table name “Table” Fails.

SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE dbo."Table"
(id int,
"Function" VARCHAR(20)) 
GO

Error Message:
Msg 102, Level 15, State 1,
Line 1 Incorrect syntax near ‘Table’.

Try using Characters Enclosed within double quotes as Literal. Fails
Example: Below statement fails.

SET QUOTED_IDENTIFIER ON
GO
SELECT "BIRADAR"

Error Message:
Msg 207, Level 16, State 1,
Line 1 Invalid column name ‘BIRADAR’.

Works
Example: Below Statement Works.

SET QUOTED_IDENTIFIER OFF
GO
SELECT "BIRADAR"
Characters Enclosed within single quotes is treated as Literal
Example:

SET QUOTED_IDENTIFIER ON
GO
SELECT 'BIRADAR'
is treated as Literal
Example:

SET QUOTED_IDENTIFIER OFF
GO
SELECT 'BIRADAR'
How to find all the objects which are created with SET QUTOED IDENTIFIER ON/OFF Below Statement can be used to find all the objects created with SET QUTOED_IDENTIFIER setting as ON:

SELECT OBJECT_NAME(object_id) 
FROM sys.sql_modules 
WHERE uses_quoted_identifier = 1
Below Statement can be used to find all the objects created with SET QUTOED_IDENTIFIER setting as OFF:

SELECT OBJECT_NAME(object_id) 
FROM sys.sql_modules
WHERE uses_quoted_identifier = 0
Which One to USE? Best practice is to use the SET QUOTED_IDENTIFIER ON setting. Try to avoid using SET QUOTED_IDENTIFIER OFF setting. As more and more new features require this setting value to be ON. For example while working with Filtered Indexes SET QUOTED_IDENTIFIER setting should be ON otherwise we will get into an exception as demonstrated in the article INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ ….

[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Char Vs Varchar
Union Vs Union All
DateTime Vs DateTime2
Stored Procedure Vs User Defined Function
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity

Please correct me, if my understanding is wrong. Comments are always welcome.