Filtered index gotcha

We added a filtered index to a table today and a SQL Agent job that updates that table started to fail with this error message:

Msg 1934, Sev 16, State 1, Line 290 : 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]

It turns out that you must have certain options enabled in your session in order to use or update a filtered index.

All these must be ON:

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER

And this one must be OFF:

  • NUMERIC_ROUNDABORT

See Books Online for details: https://msdn.microsoft.com/en-us/library/ms188783.aspx (it’s about half way down the page in the Remarks section).

So what happened here? Well it looks like SQL Agent does not, by default, set QUOTED_IDENTIFIER on. I can’t find any clear official information on this but that’s what I’m seeing in SQL Profiler. I also couldn’t find any way to override this in SQL Agent’s configuration settings.

Anyway, I added SET QUOTED_IDENTIFIER ON at the start of the job step and it worked fine.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s