Posted by Alex Kuznetsov on 01/22/07 15:11

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]