Tag Archives: SET QUOTED_IDENTIFIER ON/OFF

Difference Between SET QUOTED_IDENTIFIER ON and OFF setting in SQL Server

Differences, 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.

SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server

Sql Server,

While creating Stored Procedures, User Defined Functions etc, most of us use the SET QUOTED_IDENTIFIER ON/OFF  and SET ANSI_NULLS ON/OFF. In this article will discuss on the SET QUOTED_IDENTIFIER ON/OFF Setting.

Depending on the QUOTED_IDENTIFIER setting value either ON or OFF, SQL Server treats the data defined within double quotes or single quotes as differently. If this setting value is ON, then SQL Server treats the value defined within double quotation marks as identifier. The T-SQL rules for the identifier will not apply for these double quoted identifiers, even we can use T-SQL reserved keywords as identifier. So we can use these double quoted identifiers as Stored Procedure name, table name, user defined function name, table name, column names etc.

Let us understand this with an example:

SET QUOTED_IDENTIFIER ON GO
CREATE TABLE dbo."Table"(id int,"Function" VARCHAR(20))
GO
INSERT  INTO dbo."Table" (id,"Function") VALUES (1,'Basavaraj')
GO
SELECT id,"Function" FROM dbo."Table" GO

Result of running the above query is as below:

id          Function
----------- --------------------
1           Basavaraj
(1 row(s) affected)

In the above example we are able to use the T-SQL reserve Keywords “Table” and “Function” as the Table Name and Column Name respectively.

When this setting is ON the data/characters enclosed within single quotation mark is treated as literal. We can’t use the double quoted characters or data as literal. Below example demonstrates this behaviour, in this example the first select statement succeeds where as second select statement fails because it treats the characters enclosed within double quotation mark as identifier.

SET QUOTED_IDENTIFIER ON
 GO
 SELECT 'BASAVARAJ'
 GO
 SELECT "BIRADAR"

Result of running above statement is as below:

---------
BASAVARAJ
(1 row(s) affected)
Msg 207, Level 16, State 1, Line 1 Invalid column name 'BIRADAR'.

On the other hand if SET QUOTED_IDENTIFIER setting is OFF, then SQL Server treats the values defined within single/double quotation mark as literal. Let us understand this with below two simple examples:

Example 1) This example demonstrates that, we can’t use double quoted identifiers when SET QUOTED_IDENTIFIER setting is OFF.

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

Result of running the above sql statements:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Table'.

Example 2) This example demonstrates the fact that the values enclosed within either singles or double quotes is treated as literal when SET QUOTED_IDENTIFIER setting is OFF.

SET QUOTED_IDENTIFIER OFF GO
SELECT 'BASAVARAJ' GO
SELECT "BIRADAR"

Result of running the above statement is:

---------
BASAVARAJ
(1 row(s) affected)
-------
BIRADAR
(1 row(s) affected)

Another important point to note is that, the QUOTED_IDENTIFIER setting with which we create Stored Procedur will be stored in the meta data. So, whenever Stored Procedure executes, it will use these setting stored in the meta data. It will ignore the settings of the client or the calling application.

Below query can be used to find the objects which are created with SET QUOTED_IDENTIFIER setting as OFF.

SELECT OBJECT_NAME (object_id) FROM sys.sql_modules
WHERE uses_quoted_identifier = 0 -- 0 means OFF and 1 means ON

You would also like to read the article: Difference Between SET QUOTED_IDENTIFIERS ON and OFF settings.

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