SQL filtered index are incompatible with Dynamics GP

Using filtered index with Dynamics GP

It may be tempting to add a filtered index to a GP table, or for a table that is updated via a trigger on a GP table, however it won't work.

What is a filtered index in SQL server?

A filtered non-clustered index is a specialized data structure designed to improve the performance of queries on a specific subset of data in a table. Unlike a regular non-clustered index, which includes all rows in the table, a filtered non-clustered index is created with a defined filter condition. This means it only contains a subset of rows that meet the specified criteria. By focusing on a smaller portion of the data, filtered indexes can significantly reduce the size of the index, making it more efficient for query optimization. These indexes are particularly useful when you often query a subset of data, such as active records, archived records, or those matching a certain status, as they can speed up query execution and save disk space compared to indexing the entire table.

Problem

The problem arose when attempting to create a filtered index for non-null rows in a column of a large log table. This table was actually a log table, being written to by a SQL trigger on the IV00101 inventory table in Dynamics GP.

Error

On hitting the save button in the GP Item Maintenance form, the following error was thrown once a filtered index was added to the log table...

'IV_Item_Maintenance Save_Records_CHG on form
IV_Item_Maintenance'
'Commit() of form [748]', 0,1-,?

On examamination of the Dynamics GP SQL log file,we find the actual SQL error as...

SQLSTATE:(37000) Native Err:(1934) stmt(148472288):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]INSERT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on comp*/

Ah this is reminiscent of the issue with SQL index views and Dynamics GP, see https://timwappat.info/sql-indexed-views-are-incompatible-with-dynamics-gp/

Basically the "SET options" that GP requires to run are fundamentally incompatible with those that indexed views and filtered indexes require.

These are the set options for GP:

and this is the set options for filtered indexes:

At this time I am unaware of any work around for this, other than dropping the filter from the index.