April 23, 2008 at 5:53 pm
Thanks.
Tried that and got this:
Cannot create index because the key column 'IX_CSD_NomIPTBLB' is non-deterministic
April 24, 2008 at 8:58 pm
Andrew Shaw (4/23/2008)
Thanks.Tried that and got this:
Cannot create index because the key column 'IX_CSD_NomIPTBLB' is non-deterministic
Ah, well, that is a diferent problem. I think that you might be able to trick it into accepting it anyway, but an Index on a non-deterministic function really is a big problem. There is just no conceptual integrity to it at all, and I cannot imagine what will happen to queries & DML's if the Index values change behind it's back.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 25, 2008 at 5:54 am
How can I make my function deterministic ?
I don't pass any parameters into it and it always returns the same result, so why is it non-deterministic ?
Andrew
April 25, 2008 at 6:16 am
because it gets its values from a table and table values can be changed.
I am not sure at this point what you can do about it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 25, 2008 at 6:54 am
But thats what I want, I want to pull the value from the table incase it has changed, instead of it being hard-coded in the formula.
Is there another way round it ?
Suppose I could delete the index and live with the loss of performance that that may cause.
Andrew
April 25, 2008 at 3:21 pm
Andrew Shaw (4/25/2008)
But thats what I want, I want to pull the value from the table incase it has changed, instead of it being hard-coded in the formula.
But that cannot work for calculated column in an index though. What would happen if the calculated value changed? The materialized value in the index would still be the old value and it's position & seek-matching would still be based on the old value. Crash & burn would surely follow.
Is there another way round it ?
I think that what you really want here is an Indexed View based on your base table joined to your lookup table (IPT). Then you can index ipt_blbcode in the view and the Indexed View materialization/updating magic should keep everything in synch.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply