Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Difference Between SET QUOTED_IDENTIFIERS ON and OFF settings

4.86/5 (8 votes)
21 May 2013CPOL1 min read 60K  
Difference between SET QUOTED_IDENTIFIERS ON and OFF settings.

In this article, we will discuss the difference between SET QUOTED_IDENTIFIERS ON and SET QUOTED_IDENTIFIERS OFF. 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.

SET QUOTED_IDENTIFIERS ONSET QUOTED_IDENTIFIERS OFF
Characters Enclosed within double quotesis treated as Identifieris treated as Literal
Try using Characters Enclosed within double quotes as identifierWorks
Example: Below statement to create a table with table name “Table” succeeds.
SQL
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.
SQL
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.
SQL
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.
SQL
SET QUOTED_IDENTIFIER OFF
GO
SELECT "BIRADAR"
Characters Enclosed within single quotesis treated as Literal
Example:
SQL
SET QUOTED_IDENTIFIER ON
GO
SELECT ‘BIRADAR’
is treated as Literal
Example:
SQL
SET QUOTED_IDENTIFIER ON
GO
SELECT ‘BIRADAR’
How to find all the objects which are created with SET QUTOED_IDENTIFIERS ON/OFFBelow Statement can be used to find all the objects created with
SQL
SET QUTOED_IDENTIFIERS 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
SQL
with SET QUTOED_IDENTIFIERS_
 setting as OFF:_
SELECT OBJECT_NAME _
(object_id) _
FROM sys.sql_modules _
WHERE _
uses_quoted_identifier = 0

Visit my blog SqlHints   for many more such SQL Server Tips/Tricks.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)