user3810913 user3810913 - 3 months ago 11
SQL Question

Filtered Unique Index causing UPDATE to fail because incorrect 'QUOTED_IDENTIFIER' settings

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.

Answer

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.

Comments