title: Filtered Unique Index causing UPDATE to fail because incorrect 'QUOTED_IDENTIFIER' settings
body: <p>We've put in place the following filtered index on a table in our SQL Server 2016 database:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>Which is causing SQL code to fail now with the following error:</p>
  
  <blockquote>
    <p>UPDATE 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.</p>
  </blockquote>
  
  <p>When the filtered index is removed, the code runs perfectly.</p>
  
  <p>Looking on <a href="https://msdn.microsoft.com/en-us/library/ms186869(v=sql.110).aspx" rel="nofollow">MSDN for Index Options</a>, there's nothing about QUOTED_IDENTIFIERS.</p>
  
  <p>None of the UPDATE statements in our SQL code have double quotes for any of the values. The only double-quotes we can see are the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Even if we change those double quotes " to two single quotes '', the code still fails with the same error.</p>
  
  <p>The table itself was created with:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Like I said though, the entirety of the code runs perfectly if we do not create the filtered index; it only fails with the index.</p>
  
  <p><strong>So why is the filtered index suddenly causing our SQL to bomb and how can we fix it?</strong></p>
  
  <p><strong>UPDATE:</strong> here is a small snippet of code that reproduces the failure. This code is run through an SQL Agent Job. When the index is removed, this code runs as expected stating the error the task does not exist:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>UPDATE2 with ANSWER:</strong>
  As pointed out by the helpful answers below, I had to put</p>
  
  <pre><code>SET QUOTED_IDENTIFIER ON
  </code></pre>
  
  <p>at the top of the SQL for it to work properly.</p>
  
  <pre><code>SET QUOTED_IDENTIFIER ON
  </code></pre>
  
  <p>has NO EFFECT when I use it creating the index. </p>