Avoid computed fields in your index

I have a stored procedure that deletes records from different table. It perfectly working fine until I added an index to the table. What makes this issue confusing is I'm not able to replicate it in a different environment. So, let's start with the error that I got before I get side-track with my frustrations :)

DELETE 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.

I found this MSDN article (http://msdn.microsoft.com/en-us/library/ms190356.aspx) and the last bullet is leading me into something:

• When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

If any one of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.


It seems like the bullet above translates to this set of commands to address the issue:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF


The script above might work, but I’m not so sure if that is really what I wanted to do.... This approach will surely require some code modification and that would be a maintenance nightmare long-term.

So, I looked at the other angle of the problem. I checked if part of my index had included a computed column. VIOLA! I found one!

Removing those stink'n fields from my indexes resolved the issue.