Can aggregate functions be used in a check-constraint or a computed-column?

Hi,

Can aggregate functions (e.g. Sum, Avg) be used in a check contraint or in a table's computed column?

Thanks!

Green:

I don't think you can use subqueries for either circumstance; therefore, I am not sure that you can apply either one to a table. I think that the best you can do for a function is a scalar function. Can somebody else verify this?

Dave

|||evn i dont think its possible...as they r the values that can be entered for a particular column , while aggregate function will need a group....still will check it out soon and confirm..|||

You cannot do it directly, but as Dave suggested, you can do it by creating a function. Here's a simple example

create function SumTestOne()
returns int
as
begin
declare @.retVal int
select @.retVal = sum(one) from Test
return @.retVal
end

create table Test
(one int,
two int,
check (two > dbo.SumTestOne()))

insert Test values (100, 200)

select * from Test

|||Thanks for the suggestion to use a function. Tried it and it works well!