Code: Fixing a Filtered Index Which is Causing Data Modifications to Fail

This question came up in a webcast recently: if a filtered index is causing data modifications to fail, does disabling the filtered index fix the problem?

I wasn’t 100% sure – I couldn’t remember the last time I’d tried, if I ever had. So….

Let’s test it!

First, let’s reproduce the problem: we’ll create a filtered index on a table, then set up a session who can’t read from it due to an ANSI_SETTINGS conflict. (A list of required SET OPTIONS for filtered indexes is documented here.)

Here’s my filtered index

I created this in the WideWorldImporters sample database.

CREATE INDEX ix_SalesOrders_filtered_IsUndersupplyBackordered_OrderDate_INCLUDES ON
  Sales.Orders ( OrderDate )
  INCLUDE ( OrderID, CustomerID, SalespersonPersonID, PickedByPersonID, ContactPersonID, BackorderOrderID, ExpectedDeliveryDate, CustomerPurchaseOrderNumber, IsUndersupplyBackordered, Comments, DeliveryInstructions, InternalComments, PickingCompletedWhen, LastEditedBy, LastEditedWhen)
  WHERE (IsUndersupplyBackordered = 0);
GO

Now, let’s make our session incompatible with the index

All I have to do to make an insert fail is violate one of those required ANSI settings in my session.

Here we go!

SET ANSI_NULLS OFF;
GO

BEGIN TRAN
  INSERT Sales.Orders ( [CustomerID], [SalespersonPersonID], [PickedByPersonID], [ContactPersonID], [BackorderOrderID], [OrderDate], [ExpectedDeliveryDate], [CustomerPurchaseOrderNumber], [IsUndersupplyBackordered], [Comments], [DeliveryInstructions], [InternalComments], [PickingCompletedWhen], [LastEditedBy], [LastEditedWhen])
  SELECT TOP (1) [CustomerID], [SalespersonPersonID], [PickedByPersonID], [ContactPersonID], [BackorderOrderID], [OrderDate], [ExpectedDeliveryDate], [CustomerPurchaseOrderNumber], [IsUndersupplyBackordered], [Comments], [DeliveryInstructions], [InternalComments], [PickingCompletedWhen], [LastEditedBy], [LastEditedWhen]
  FROM Sales.Orders;
GO

This results in the Error 1934

Msg 1934, Level 16, State 1, Line 25 INSERT failed because the following SET options have incorrect settings: ‘ANSI_NULLS’. 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.

Now to disable the filtered index

I suck all the pages out of the filtered index, leaving only the metadata behind:

ALTER INDEX ix_SalesOrders_filtered_IsUndersupplyBackordered_OrderDate_INCLUDES ON
  Sales.Orders DISABLE;
GO

And now, when I retry my insert….

(1 row affected)

It worked! As long as this filtered index is disabled, the insert works again for the session with the incompatible ANSI_NULLS setting.

Personally, I’d rather drop the filtered index

Rebuilding the filtered index will cause this problem to happen again – until all sessions modifying the table have the required se ssion settings in place.

So to prevent actual rebuilds, I’d rather drop the index until everything is in order for the index to be recreated.