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 ON |
SET QUOTED_IDENTIFIERS 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 ON
GO
SELECT ‘BIRADAR’ |
How to find all the objects which are created with SET QUTOED_IDENTIFIERS ON/OFF |
Below Statement can be used to find all the objects created with 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 with SET QUTOED_IDENTIFIERS_
setting as OFF:_
SELECT OBJECT_NAME _
(object_id) _
FROM sys.sql_modules _
WHERE _
uses_quoted_identifier = 0 |
推荐.NET配套的通用数据层ORM框架:CYQ.Data 通用数据层框架
发表评论
5VAHtO wow, awesome blog post.Really thank you! Awesome.