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: |
| Try using Characters Enclosed within double quotes as Literal. | Fails Example: Below statement fails. SET QUOTED_IDENTIFIER ON GO SELECT "BIRADAR" Error Message: |
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.
Good post! The other difference is that setting QUOTED_IDENTIFIER OFF prevents certain functionality. From technet:
“SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.”
This is the case with SQL server versions 2005 and up.
hi,
basaraj
Your article is very nice.
please post about event in sql server
Thank you Deepak. Will try to do that.
Hello.
Thanks a lot..for your information…..
Thanks a lot for this post and It’s really very useful article. Keep it up.
Thank you Vijay
Hi Basavaraj
Can you pls explain how non clustred index work in absence of clustred index .
Thanks in advance
Nice
Thanks Gunashekar. I’m glad you found it useful.