April 18, 2014 at 12:10 pm
I would like to create a persisted computed column and put an index on it but given all of Microsoft's restrictions, I'm not sure that I can. The problem is the list of required set options. I can control the set options when the index is created and in the case of the selects which will use the index (at least in the case of those that I care about) but I don't think I can control the set options on every insert, update or delete statement which might change values in the index. Books online doesn't say what will happen if I don't control those -- does anyone here know? Also, if you know of any relevant differences in SQL Server 2008 or 2012 I would appreciate hearing about that -- I'm using 2012 but we have to support users who have versions as old as 2005.
Thanks for any input you can offer.
April 18, 2014 at 12:48 pm
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.
April 18, 2014 at 1:17 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply