|
|
Posted by Alex Kuznetsov on 01/22/07 18:39
SQLMan_25 wrote:
> Alex,
> once again thanks for your prompt reply. I will consider your
> suggestion. There are always more than one way to skin the cat..lol. I
> have other solutions for the same issue. I only need this info for
> display purposes.
> My original question was why my function is considered
> non-deterministic. Any ideas, articles, or links?
>
> Thanks,
> NH
> Alex Kuznetsov wrote:
> > 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/
It is non-deterministic by definition - which means that you cannot
guarantee that it will always return the same result for the same input
parameter.
-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
Navigation:
[Reply to this message]
|