|
|
Posted by SQLMan_25 on 01/22/07 18:16
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/
Navigation:
[Reply to this message]
|