UDF and persisted column

[SQLMan_25]

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

[Alex Kuznetsov]

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/

[SQLMan_25]

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/

[Alex Kuznetsov]

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/

[SQLMan_25]

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/

[Alex Kuznetsov]

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/