We've put in place the following filtered index on a table in our SQL Server 2016 database:
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
Which is causing SQL code to fail now with the following error:
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.
When the filtered index is removed, the code runs perfectly.
Looking on MSDN for Index Options, there's nothing about QUOTED_IDENTIFIERS.
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:
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
Even if we change those double quotes " to two single quotes '', the code still fails with the same error.
The table itself was created with:
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
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.
So why is the filtered index suddenly causing our SQL to bomb and how can we fix it?
UPDATE: 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:
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 with ANSWER: As pointed out by the helpful answers below, I had to put
SET QUOTED_IDENTIFIER ON
at the top of the SQL for it to work properly.
SET QUOTED_IDENTIFIER ON
has NO EFFECT when I use it creating the index.
There is: SET QUOTED_IDENTIFIER (Transact-SQL)
In order to prevent similar issues, I would recommend to check the exact requirements for creating a filtered index: CREATE INDEX (Transact-SQL). It has a nice neat table that shows SET options required for a filtered index to be created.