SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


indexed persisted computed columns


indexed persisted computed columns

Author
Message
lnoland
lnoland
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 821
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.
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10637 Visits: 13559
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
lnoland
lnoland
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 821
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search