Interesting problem with SQL Server, ARITHABORT, filtered indexes, calculated columns, and compatibility mode of 80

Posted on by

I recently ran into a problem in a legacy application.  Someone had applied a unique index on a column in a SQL Server database table.  Shortly after, we started seeing some errors in our logs relating to that table:

UPDATE failed because the following SET options have incorrect settings: ‘ARITHABORT’. 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.

After a good amount of investigation, I discovered that it was related to the fact that the unique index as a filtered index (it needed to be a filtered unique index because the business rules allowed for duplicate null values, but not duplicate non-null values) and it turns out that if you use a filtered index in SQL Server while running with Compatibility Mode = 80, then any updates to that table need to specify SET ARITHABORT ON, or it will fail with this error.

Well, this is a legacy application, which can’t have the compatibilty mode changed for numerous reasons, and there are many places where we would need to set ARITHABORT, so that wasn’t really a good option either.

First I tried a work around using a computed column that would never be null, and then I applied a unique index to that column, but the same ARITHABORT problem exists with computed columns as it does with filtered indexes.

After a few other attempts at a workround, my friend Phil @ XDev Group mentioned a method he used to solve a similar problem.  I created a trigger that would create a poor man’s calculated column, without the imposed limitations of using a real calculated column in compat80 mode.

CREATE TRIGGER [dbo].UniqueJobNumberGenerator ON [dbo].jobdata
    FOR INSERT, UPDATE
AS
    BEGIN
        SET NOCOUNT ON;

        UPDATE  dbo.jobdata
        SET     UniqueJobNumber = ( CASE WHEN [num] IS NULL
                                         THEN CONVERT([varchar], [id], ( 0 ))
                                              + '00000000'
                                         ELSE [num]
                                    END )
        WHERE   id IN ( SELECT   id
                               FROM     INSERTED )

    END

Then I applied a unique index on that column (without a filter) and … success!

One thought on “Interesting problem with SQL Server, ARITHABORT, filtered indexes, calculated columns, and compatibility mode of 80”

  1. Thanks for this writeup – you just saved me a couple of hours of frustrating troubleshooting! 🙂

    I just had this error appear “everywhere” after I had recently added a filtered index to a busy table, but had no idea what was causing the error message. Your page was one of the first search results coming up, and described perfectly what was happening (filtered index, compat mode 80, legacy application). In my case though, the only available fix was to remove the filtered index again, as it was intended to be a more efficient lookup index than the main clustered index. I guess I’ll have to come up with something else.

    Reply

Leave a comment