QUOTED_IDENTIFIER 选项对 index 的影响

时间：2021-06-21 23:20:32

早上来到公司，发现用于整理索引碎片的Job跑失败了，查看job history，发现以下错误消息：

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。

微软Docs给出的解释是：

  • 当你在计算列（computed column）或索引视图（idnexed view）上创建（create）或修改（alter）索引时，必须设置SET QUOTED_IDENTIFIER =ON。
  • 当你创建过滤索引时，必须设置SET QUOTED_IDENTIFIER =ON。
  • 当调用XML数据类型方法时，必须设置SET QUOTED_IDENTIFIER =ON。

从上面的描述可以看出，QUOTED_IDENTIFIER选项设置为ON是非常重要的，事实上，该选项默认设置为ON。用户可以通过下面三种方式来查看SET选项的默认值：

DBCC USEROPTIONS

select db.name

    ,db.database_id

    ,db.is_quoted_identifier_on

from sys.databases db

select *

from sys.dm_exec_sessions

where session_id=@@SPID

QUOTED_IDENTIFIER的作用

QUOTED_IDENTIFIER 选项的作用是强迫SQL Server遵循ISO的规则，把双引号和中括号作为标识符的界定符，用单引号来表示字符串。默认情况下，SQL Server的QUOTED_IDENTIFIER 选项的配置值是ON，这就意味着，界定符是中括号 [] 或 双引号。标识符是指变量、表、存储过程、函数等数据库对象的名称。

当SET QUOTED_IDENTIFIER选项设置为ON时，标识符可以被双引号和中括号界定，字符串必须使用单引号界定，举个例子，"var 1" 是一个标识符，等价于[var 1]，字符串智能使用单引号，'str 1'，也就是说，使用双引号的字符串被解释为一个object的标识符。

当SET QUOTED_IDENTIFIER选项设置为OFF时，标识符的界定必须遵守TSQL规则，字符串使用单引号或双引号，标识符只能使用中括号。

1，界定符影响标识符

当 SET QUOTED_IDENTIFIER=ON时，SQL Server将双引号作为界定符，功能和默认定界符 中括号 [] 相同，而中括号作为定界符是不受Quoted_Identifier 选项设置的影响，始终可以作为定界符使用。

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

SET QUOTED_IDENTIFIER OFF

GO

--fail

CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);

GO

SET QUOTED_IDENTIFIER ON;

GO

-- 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

2，字符串表示

当 SET QUOTED_IDENTIFIER=ON时，字符串必须使用单引号；当SET QUOTED_IDENTIFIER=OFF时，字符串可以使用使用单引号，也可以使用双引号

SET QUOTED_IDENTIFIER ON;

GO

--succed

select 'abc'

go

--fail

select "abc"

GO

SET QUOTED_IDENTIFIER OFF;

GO

--succed

select 'abc'

go

--succed

select "abc"

GO

报错信息：Invalid column name 'abc'，很奇怪的错误信息。当设置QUOTED_IDENTIFIER为ON时，双引号标识的字符串会被解释为一个 Object 的标识符。

SET QUOTED_IDENTIFIER选项的使用

1，修改SET QUOTED_IDENTIFIER选项，在当前session中设置 SET QUOTED_IDENTIFIER 语句只会影响当前的Session，并且是在Parse 时设置的。

2，SET QUOTED_IDENTIFIER和 SET ARITHABORT 搭配使用

在使用SQL Server Agent删除数据的时候，SQL Server有时会抛出错误消息：

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 option设置为ON

SET ARITHABORT ON

SET QUOTED_IDENTIFIER ON

通常这两个选项的默认值是ON，当出现错误时，应该知道在创建或修改Index时，需要在语句执行前设置：SET QUOTED_IDENTIFIER ON 和 SET ARITHABORT ON。

参考文档：

SET QUOTED_IDENTIFIER (Transact-SQL)

相关文章