开发者社区> 长征6号> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

QUOTED_IDENTIFIER 选项对 index 的影响

简介:
+关注继续查看

在修改或创建Index时,有时会收到一些Error Message,指明Set options设置错误,最常见的选项是:SET QUOTED_IDENTIFIER 选项

  1. 当创建或修改的index包含computed columns ,必须 SET QUOTED_IDENTIFIER=ON;
  2. 当创建或修改Indexed View上的Index时,必须 SET QUOTED_IDENTIFIER=ON;
  3. 当创建或修改filtered index时,必须 SET QUOTED_IDENTIFIER=ON;

一,Case

早上,发现用于处理Index fragmentation的Job跑失败了,查看job history,发现以下Error Message:

ALTER INDEX 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. [SQLSTATE 42000] (Error 1934).  The step failed.

错误原因是:没有 SET QUOTED_IDENTIFIER =ON。

MSDN的解释是:

  • 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.
  • SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.
  • SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

二,SET QUOTED_IDENTIFIER 选项的作用,将双引号作为定界符,SQL Server默认的定界符是:[]

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks.

When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

当设置QUOTED_IDENTIFIER时,使用双引号的字符串都会被解释为一个 Object 的标识,因此,如果要标识一个字符串,必须使用单引号。

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks ("). SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

1,delimiting identifiers

当 SET QUOTED_IDENTIFIER=ON时,SQL Server将双引号作为界定符,功能和默认定界符 中括号 [] 相同。

而中括号作为定界符是不受Quoted_Identifier 选项设置的影响的,始终可以作为定界符使用。

Using brackets, [ and ], to delimit identifiers is not affected by the QUOTED_IDENTIFIER setting.

 

select是关键字,不能用于用户定义的object,除非使用定界符。当SET QUOTED_IDENTIFIER ON 时,可以使用双引号“select”,这样select关键字就能作为Table Name,作用和[select]相同。

复制代码
SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SELECT "identity","order" 
FROM "select"
ORDER BY "order";
GO

DROP TABLE "SELECT";
GO

SET QUOTED_IDENTIFIER OFF;
GO
复制代码

2,当 SET QUOTED_IDENTIFIER=ON时,字符串必须使用单引号

当SET QUOTED_IDENTIFIER=OFF时,字符串可以使用使用单引号,也可以使用双引号

复制代码
SET QUOTED_IDENTIFIER ON;
GO
--success
DECLARE @var varchar(10)
set @var='abc'
select @var
GO

--failure
DECLARE @var varchar(10)
set @var="abc"
select @var
GO


SET QUOTED_IDENTIFIER OFF;
GO
--success
DECLARE @var varchar(10)
set @var='abc'
select @var
GO

--success
DECLARE @var varchar(10)
set @var="abc"
select @var
GO
复制代码

报错信息:非法的Column Name,很奇怪的错误信息。当设置QUOTED_IDENTIFIER时,双引号标识的字符串会被解释为一个 Object 的标识。
Invalid column name 'abc'.

二, 从 sys.databases 中查看到Quoted_Identifier 选项的设置

select db.name,db.database_id,  db.is_quoted_identifier_on
from sys.databases db

三, Scope

Set QUOTED_IDENTIFIER 语句只会影响当前的Session,并且是在Parse 时设置的。

SET QUOTED_IDENTIFIER is set at parse time. Setting at parse time means that if the SET statement is present in the batch or stored procedure, it takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed. When multiple conflicting SET statements are present in the batch, the last setting parsed is used.

四,和 Set ArithAbort 搭配使用

在使用Sql Server Agent删除数据的时候,SQL Server抛出Error:

DELETE 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. [SQLSTATE 42000] (Error 1934).  The step failed.

解决方案是:在 delete 语句之前加入两个set options

SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON

在创建或修改Index时,记得在语句前面加上:SET QUOTED_IDENTIFIER ON 和 SET ARITHABORT ON。

参考文档:

SET QUOTED_IDENTIFIER (Transact-SQL)

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。





本文转自悦光阴博客园博客,原文链接:http://www.cnblogs.com/ljhdo/p/5177061.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
vue :for循环根据index动态绑定css样式
vue :for循环根据index动态绑定css样式
49 0
how do you usually upload picture in SCN A workaround for current SCN upload is
how do you usually upload picture in SCN A workaround for current SCN upload is
45 0
How to open a given CDS view in ABAP development studio
Requirement suppose I have a view name ZC_SALESORDERITEMQ. I need to open it in ADS. solution SE16:
23 0
How to open a given CDS view in ABAP development studio
How to open a given CDS view in ABAP development studio
28 0
Think Python: How to Think Like a Computer Scientist
Think Python: How to Think Like a Computer Scientist:http://greenteapress.com/thinkpython/html/index.html
787 0
vb int string 互转
s   =   CStr(i)         i   =   CInt(s)
647 0
+关注
1222
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载