November 18, 2014 at 10:16 am
Hello!
Can I create a PERSISTED computed column where the value (decimal) is get by a Function ?
See the example:
-- Sales
create table sales(
idSale int identity not null primary key);
go
-- Sales Items
create table salesItems(
idSaleItems int identity not null primary key,
idSale int not null foreign key references sales(idSale),
valueItem decimal(12,2));
go
-- Function to get the full amount of the sale
create function dbo.fu_salesTotal(@idSale int) returns decimal(12,2)
as
begin
declare @value decimal(12,2);
select @value = sum(valueItem)
from salesItems
where idSale = @idSale
return isnull(@value, 0);
end;
go
-- add computed column
alter table sales
add total as dbo.fu_salesTotal(idSale) PERSISTED
Error:
Computed column 'total' in table 'sales' cannot be persisted because the column is non-deterministic.
I've tried adding WITH schemabinding in a Function but it is not possible..
Could you please help me?
Thanks!
Jose Anchieta Carvalho Junior
November 18, 2014 at 10:28 am
JoseACJr (11/18/2014)
Hello!Can I create a PERSISTED computed column where the value (decimal) is get by a Function ?
See the example:
-- Sales
create table sales(
idSale int identity not null primary key);
go
-- Sales Items
create table salesItems(
idSaleItems int identity not null primary key,
idSale int not null foreign key references sales(idSale),
valueItem decimal(12,2));
go
-- Function to get the full amount of the sale
create function dbo.fu_salesTotal(@idSale int) returns decimal(12,2)
as
begin
declare @value decimal(12,2);
select @value = sum(valueItem)
from salesItems
where idSale = @idSale
return isnull(@value, 0);
end;
go
-- add computed column
alter table sales
add total as dbo.fu_salesTotal(idSale) PERSISTED
Error:
Computed column 'total' in table 'sales' cannot be persisted because the column is non-deterministic.
I've tried adding WITH schemabinding in a Function but it is not possible..
Could you please help me?
Thanks!
Jose Anchieta Carvalho Junior
The problem is that the function isn't deterministic. Each time it is called for a specific row of data, the value returned can change based on the data queried by the function. To be deterministic the value needs to remain constant each time it is invoked for a row of data.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply