Posted by SQLMan_25 on 01/22/07 15:31

Alex,
Thank you for your reply. Computed persisted colum is a business need.
I want when price of one item changes, it should be reflected in
computed column. Otherwise I have do these updates via jobs and
calculate this value. I want to persist so I can build index on it etc
etc.

Regards,
NH
Alex Kuznetsov wrote:
> SQLMan_25 wrote:
> > Hi All,
> >
> > I am trying to create a user defined function that fetches the price of
> > an item. I have written a scalar function that takes itemid and returns
> > its price.
> > Simple version of tables would be like this:
> >
> > Item(
> > itemid int,--pk
> > price decimal(18,4)
> > )
> >
> > item_detail(
> > redid int , --pk
> > itemid int, --fk
> > cust_id int,--fk
> > order_qty decimal(18,4)
> > )
> >
> > Now I want to create a computed persisted column having formula
> >
> > dbo.GetPrice(Itemid) * order_qty
> >
> > I get the error cannot be persisted because column is
> > non-deterministic.
> >
> > function is as following:
> >
> > create function GetPrice(@itemid int)
> > Returns decimal(18,4)
> > AS
> > Begin
> > declare @price decimal(18,4)
> >
> > select @price =price from Item where itemid=@itemid
> >
> > return @price
> > End
> > Go
> >
> > I would appreciate if someone can shed some light that why this
> > function is considered non-deterministic by SQL Server 2005. Is there
> > any work around this behaviour?
> > Thanks in advance for all your remarks.
> > NH
>
> You don't want to do things like this.
> Suppose SQL Server let you store your computed value. Suppose later
> somebody changed the relevant Item.price. Now you have a stale value in
> your persisted column and you do not know about it.
> Why can't you just select?
>
> -----------------------
> Alex Kuznetsov
> http://sqlserver-tips.blogspot.com/
> http://sqlserver-puzzles.blogspot.com/

[Back to original message]