Posted by Alex Kuznetsov on 01/22/07 16:07

SQLMan_25 wrote:
> 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/


Under most circumstances I would just join these two tables. having an
index on (itemID, price), the join should not add too much overhead.
If, however, you really want to denormalize and store price in the
child table, you can use ON UPDATE CASCADE to do all dirty work for
you:

CREATE TABLE Items(ItemID INT NOT NULL PRIMARY KEY,
Price DECIMAL(10,2),
CONSTRAINT UK_Items UNIQUE(ItemID, Price)
)
go
CREATE TABLE OrderDetail(ItemID INT NOT NULL,
Qty INT NOT NULL,
Price DECIMAL(10,2),
CONSTRAINT FK_OrderDetail_Items FOREIGN KEY(ItemID, Price) REFERENCES
Items(ItemID, Price) ON UPDATE CASCADE
)
go
SET NOCOUNT ON
INSERT Items VALUES(1, 24.95)
INSERT Items VALUES(2, 2.99)
INSERT OrderDetail VALUES(1, 5, 24.95)
INSERT OrderDetail VALUES(2, 3, 2.99)
SELECT * FROM OrderDetail
/*
ItemID Qty Price
----------- ----------- ------------
1 5 24.95
2 3 2.99
*/
UPDATE Items SET Price = 19.95 WHERE ItemID = 1
SELECT * FROM OrderDetail
/*
ItemID Qty Price
----------- ----------- ------------
1 5 19.95
2 3 2.99
*/

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[Back to original message]