• LutzM (4/18/2014)


    As per BOL the index will simply being ignored if the options are set incorrectly.

    The connection on which the index is created, and all connections trying INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings.

    ...

    The easiest way to see what happens is to set up a test scenario with a computet (persisted) indexed column and test different option settings.

    Well, technically, Books Online says that the index will be ignored on any "Select" statement on a connection which does not have those settings. As I said, I can control the settings on the select. It doesn't say what will happen if the settings don't exist for an INSERT, UPDATE or DELETE statement. And while I can run tests to try to determine what happens, it will only tell me what happens in the case of those tests -- it won't necessarily create the situation that caused Microsoft to raise the ban, which might be one isolated case which doesn't work. So I was hoping that someone here might have access to an authoritative source with a more thorough explanation.

    The main reason I ask is because I cannot see any reason at all for the restriction on INSERTs, UPDATEs, and DELETEs. If I were to put a trigger on the table and whenever the underlying column changed, I changed another column using the exact same expression used in the persisted computed column, it would accept it with no restrictions at all on what set options existed for the insert, update or delete which invoked the trigger. While that is a workaround, it seems a bit ridiculous that I would need to go to that much trouble to avoid a restriction which, on the face of it, doesn't seem to have any justification.

    Thanks anyway for the reply.