Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

indexed persisted computed columns Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 12:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:36 AM
Points: 113, Visits: 559
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.
Post #1563127
Posted Friday, April 18, 2014 12:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1563140
Posted Friday, April 18, 2014 1:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:36 AM
Points: 113, Visits: 559
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.
Post #1563150
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse