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!