正文 

当前位置： 首页 > 源码 >

过滤的唯一索引导致更新失败，因为不正确的 'QUOTED_IDENTIFIER' 设置

     2023-02-15     101

关键词：

【中文标题】过滤的唯一索引导致更新失败，因为不正确的 \'QUOTED_IDENTIFIER\' 设置【英文标题】：Filtered Unique Index causing UPDATE to fail because incorrect 'QUOTED_IDENTIFIER' settings过滤的唯一索引导致更新失败，因为不正确的 'QUOTED_IDENTIFIER' 设置 【发布时间】：2016-12-21 05:42:18 【问题描述】：

我们在 SQL Server 2016 数据库中的表上设置了以下过滤索引：

    CREATE UNIQUE NONCLUSTERED INDEX [fix_SystemPKeyExecutionOrder] ON [DataInt].[TaskMaster]
(
    [SystemPkey] ASC,
    [ExecutionOrder] ASC
)
WHERE ([ExecutionOrder] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95)
GO

这导致 SQL 代码现在失败并出现以下错误：

更新失败，因为以下 SET 选项不正确 设置：'QUOTED_IDENTIFIER'。验证 SET 选项是否正确 与索引视图和/或计算列上的索引一起使用和/或 过滤索引和/或查询通知和/或 XML 数据类型 方法和/或空间索引操作。 [SQLSTATE 42000]（错误 1934）。步骤失败。

过滤后的索引去掉后，代码运行完美。

查看MSDN for Index Options，没有关于 QUOTED_IDENTIFIERS 的内容。

我们的 SQL 代码中的任何 UPDATE 语句都没有任何值的双引号。我们可以看到的唯一双引号如下：

SET @ROWCOUNT = @@ROWCOUNT

    If (@ROWCOUNT = 0)
    BEGIN
        RAISERROR('The "File Import" task ACTIVE_YN could not be updated to "Y". Either the task does not exist or the system "File Import To Stage" does not exist.', 16, 1)
    END
    ELSE
    BEGIN
        Print 'Successfully updated the "File Import" task ACTIVE_YN to "Y".'
    END

即使我们将这些双引号 " 更改为两个单引号 ''，代码仍然会失败并出现相同的错误。

表本身是通过以下方式创建的：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [DataInt].[TaskMaster](
    [Pkey] [bigint] IDENTITY(1,1) NOT NULL,
    [ScheduleMasterPkey] [int] NOT NULL,
    [SystemPkey] [int] NOT NULL,
    [SourcePkey] [int] NOT NULL,
    [TargetPkey] [int] NOT NULL,
    [TaskName] [varchar](255) NOT NULL,
    [TaskTypePkey] [int] NOT NULL,
    [Active_YN] [char](1) NOT NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [varchar](100) NULL,
    [RowVersion] [timestamp] NOT NULL,
    [ExecutionOrder] [int] NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [Pkey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY],
 CONSTRAINT [uc_TaskName] UNIQUE NONCLUSTERED 
(
    [TaskName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]

GO

就像我说的，如果我们不创建过滤索引，那么整个代码都可以完美运行；它只会因索引而失败。

那么为什么过滤后的索引会突然导致我们的 SQL 被炸毁，我们该如何解决呢？

更新：这里是重现故障的一小段代码。此代码通过 SQL 代理作业运行。删除索引后，此代码按预期运行，说明任务不存在的错误：

DECLARE @ROWCOUNT INT = 0

UPDATE [DataIntegrationMaster].[DataInt].[TaskMaster]
    Set Active_YN = 'Y'
    where TaskName = 'File Import'
    and SystemPkey = 0

    SET @ROWCOUNT = @@ROWCOUNT

    If (@ROWCOUNT = 0)
    BEGIN
        RAISERROR('The "File Import" task ACTIVE_YN could not be updated to "Y". Either the task does not exist or the system "File Import To Stage" does not exist.', 16, 1)
    END
    ELSE
    BEGIN
        Print 'Successfully updated the "File Import" task ACTIVE_YN to "Y".'
    END

UPDATE2 的答案： 正如下面有用的答案所指出的，我不得不提出

SET QUOTED_IDENTIFIER ON

位于 SQL 的顶部以使其正常工作。

SET QUOTED_IDENTIFIER ON

当我使用它创建索引时没有效果。

【问题讨论】：

您是否能够创建一个最小但自包含的脚本，我们可以使用它来重现问题？ 用代码 sn-p 更新了导致错误的问题。 【参考方案1】：

正如@Roger Wolf 的回答中指出的那样，创建过滤索引需要您将QUOTED_IDENTIFER 设置设置为ON，这就是您所做的。如果您不这样做，您一开始就无法创建过滤索引。

但是，一旦创建，似乎对该表的任何 DML 操作（不仅仅是更新）都需要您将 QUOTED_IDENTIFER 设置也设置为 ON。这是您目前缺少的内容，也是您收到错误的原因。

所以，我不知道您的 update 的上下文是什么，无论您是作为临时语句运行它，还是它是存储过程的一部分。无论哪种方式，请确保在开头的某处包含 SET QUOTED_IDENTIFIER ON 语句。

【讨论】：

感谢您的更新。我对你投了赞成票，但给了@Roger 答案功劳，因为他首先获得了与信息的链接。正如您所说，我必须将SET QUOTED_IDENTIFIER ON 放入SQL 中。该 SQL 是一项常规代理工作。我发现现在编写的任何涉及该表的 SP 或其他 SQL 都必须明确设置 SET QUOTED_IDENTIFIER ON，这很烦人。【参考方案2】：

有：SET QUOTED_IDENTIFIER (Transact-SQL)

为了防止出现类似问题，我建议检查创建过滤索引的确切要求：CREATE INDEX (Transact-SQL)。它有一个漂亮整洁的表格，显示了创建过滤索引所需的SET 选项。

【讨论】：

良好的链接@Roger。我想知道为什么 MSDN 不能将这些信息与 OP 链接到的页面放在同一页面上。 我必须将SET QUOTED_IDENTIFIER ON 放在实际执行更新的 SQL 的顶部；如果我把SET QUOTED_IDENTIFIER ON 放在索引的创建中它没有效果。现在它正在工作。谢谢你的链接。那是一个 MSDN 很好的链接。我发现现在编写的任何涉及该表的 SP 或其他 SQL 都必须明确设置 SET QUOTED_IDENTIFIER ON，这很烦人。 您可以尝试在服务器级别默认设置此选项ON，从而最大限度地减少对现有代码的影响。除非此选项在某处明确设置为OFF，否则每个人都会选择服务器默认值。

mysql建索引导致insert失败

...BA索引，走那个索引，需要mysql会把sql优化，看A列的数据过滤多，还是B的过滤多；，如果A的列数据能过滤更多数据，那么会走AB,如果B的列能过滤更多数据，则走BA；大家都知道，一条查询语句走了索引和没走索引的查询效率是... 查看详情

为啥优化器不使用我的唯一过滤索引？

】为啥优化器不使用我的唯一过滤索引？【英文标题】：Whyisn\'ttheoptimizerusingmyuniquefilteredindex?为什么优化器不使用我的唯一过滤索引？【发布时间】：2014-09-2616:31:51【问题描述】：示例架构：createtabledbo.Person(IDintidentity(1,1)notnullc... 查看详情

mysql过滤重复数据的问题

...据！数据库中有一些数据！现在想导入另外的数据！彻底过滤掉重复内容，保留原始存在的数据！有没有简单的办法可以解决呢？思路：数据库如果存在就更新，不存在就插入首先：1.对你要设置的表，点击右键--管理索引--新建... 查看详情

UITableView 过滤后的数据索引不正确

】UITableView过滤后的数据索引不正确【英文标题】：UITableViewfiltereddatahasincorrectindex【发布时间】：2015-10-0114:20:23【问题描述】：这是我从CoreData过滤数据的代码，它提供了更正的数据，但如果我查看记录的详细信息，它会给我错... 查看详情

每日一记--索引/过滤器(代码片段)

打卡的时间不一定要在晚上，应该要学会灵活应对。然后由于每天脑袋中会冒出许多其他的问题，所以也打算记下来，免得下次忘记又得去查看资料。 1、主键索引与唯一索引的区别？一直以来都不知道主键索引与唯一索引... 查看详情

由于在同一 SaveChanges 中删除的记录中的值，实体更新在唯一索引上失败

】由于在同一SaveChanges中删除的记录中的值，实体更新在唯一索引上失败【英文标题】：EntityupdatefailsonuniqueindexbecauseofvalueinrecorddeletedinthesameSaveChanges【发布时间】：2016-06-2013:13:09【问题描述】：给定以下Persons表：CREATETABLEPersons(I... 查看详情

删除非不同行

...发布时间】：2011-08-0217:30:13【问题描述】：我有一个具有唯一非聚集索引的表，其中4列列在该索引中。我想更新表中的大量行。如果我这样做，它们将不再是不同的，因此更新会因为索引而失败。我想禁用索引，然后删除最旧... 查看详情

聚集索引和唯一索引的区别是啥？

参考技术A聚集索引并不一定是唯一索引。\\r\\n主键是唯一的，所以创建了一个主键的同时，也就这个字段创建了一个唯一的索引，唯一索引实际上就是要求指定的列中所有的数据必须不同。主键一唯一索引的区别：\\r\\n1一个表... 查看详情

oracle中使用exp命令导出数据过程中断网了导致终止失败，该怎么操作？

...大疑问：第一，索引范围扫描（indexrangescan）第二，索引唯一扫描（indexuniquescan），下面就这两种疑问通过图文并茂的方式给大家详细介绍下：1、索引范围扫描（indexrangescan）复制代码代码如下:selectempno,enamefromempwhereempno>1orderb... 查看详情

聚集索引必须是唯一的吗？

】聚集索引必须是唯一的吗？【英文标题】：Doclusteredindexeshavetobeunique?【发布时间】：2011-05-1823:50:16【问题描述】：如果聚集索引不是唯一的会发生什么？是否会因为插入的行流向某种“溢出”页面而导致性能下降？它是“制造... 查看详情

主键和索引的区别

 1.   主键一定是唯一性索引，唯一性索引并不一定就是主键   所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记... 查看详情

数据库索引

常见索引的基本概念约束：主键约束【主键索引】唯一约束【唯一索引】外键约束【外键索引】索引：聚集索引联合索引覆盖索引部分索引【前缀索引】索引的优缺点优点：提高数据检索效率提高表之间的JOIN效率利用唯一性索... 查看详情

oracle中的统计信息问题

为什么统计信息会导致索引失效，我把统计信息删掉后，查询快了好多，有统计信息时，执行计划不走索引统计信息需要及时更新才会发挥它的作用，旧的统计信息不仅无用，而且会导致查询优化器使用不正确的执行计划，导致... 查看详情

sqlserver2005如何设置一个或几个字段唯一约束？

唯一约束是该字段的值不允许重复吗？参考技术A可用sql语句添加唯一约束altertable表名addconstraint约束名uniquenonclustered(指定唯一约束的列名)这样就可以了，我们都是用sql语句，在控制台也可以的！ 参考技术B　　唯一索引和约束... 查看详情

按自定义字段过滤帖子 - url 正确更新但过滤不起作用

】按自定义字段过滤帖子-url正确更新但过滤不起作用【英文标题】：filteringpostsbycustomfields-urlupdatescorrectlybutfilteringdoesn\'twork【发布时间】：2021-12-2513:27:40【问题描述】：我正在尝试通过使用ACFpro创建的几个自定义字段过滤自定... 查看详情

创建索引和唯一约束的最佳方法是啥？

...束的最佳方法是什么？我的大部分查询将基于date列进行过滤。如果我在date上创建单列索引和一个包含（日期和键）的唯一约束，我最终会创建两个索引，因为唯一约束也会创 查看详情

解决mui通讯录索引列表，反复加载导致的搜索不正确问题

按照mui提供的索引列表加载展示数据，在多次加载时会出现搜索不正常的问题，经过检查发现每重新加载一次数据就会重新注册input的input事件，我的想法是保证只有一个window.indexedList=newmui.IndexedList(list);操作的实... 查看详情

重新索引数据框的正确方法？ [复制]

...布时间】：2021-09-1613:02:54【问题描述】：我有一个按位置过滤的大型数据集。最终结果是这样的：column1column20a1106b2178c3我猜想索引值在整个地方都在跳过，因为具有相同位置的所有列都不连续。为了重置索引，我做了df.reindex(inde... 查看详情