Computed Column Specification

Hello,

I want to assign a column a computed value, which is the multiplication

of a value from the table within and a value from another table.

How can I do that?

Say the current table is A, column1; and the other table is B, column3.

What should I write as formula?

I tried someting like;

column1 * (SELECT column3 FROM B WHERE A.ID = B.ID)

but it didn't work.On 10 Sep 2006 14:41:16 -0700, Dot Net Daddy wrote:

Quote:

Originally Posted by

>Hello,
>
>I want to assign a column a computed value, which is the multiplication
>
>of a value from the table within and a value from another table.

(snip)

Hi Dot Net Daddy,

Here's what Books Online has to say:

Quote:

Originally Posted by

>computed_column_expression
>Is an expression that defines the value of a computed column. (...) The
>expression can be a noncomputed column name, constant, function,
>variable, and any combination of these connected by one or more
>operators. The expression cannot be a subquery or contain alias data
>types.

So you can't embed a subquery in a computed column expression, but
user-defined functions are okay.

CREATE dbo.MyUDF (@.A_ID AS int)
RETURNS int
AS
RETURN (SELECT column3 FROM B WHERE B.ID = @.A_ID)
go

And then defined the computed column as

ALTER TABLE MyTable
ADD NewColumn AS column1 * dbo.MyUDF(ID)
go

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

Quote:

Originally Posted by

So you can't embed a subquery in a computed column expression, but
user-defined functions are okay.
>
CREATE dbo.MyUDF (@.A_ID AS int)
RETURNS int
AS
RETURN (SELECT column3 FROM B WHERE B.ID = @.A_ID)
go
>
And then defined the computed column as
>
ALTER TABLE MyTable
ADD NewColumn AS column1 * dbo.MyUDF(ID)

However, this is not a very good idea. The performance cost can be horrible.

I once made an experiment where I added a CHECK constraint which included
a UDF, and that replaced a trigger test. Inserting 25000 rows into the table
took 1-2 seconds without the UDF, and 30 seconds with.

So in the end, defining a view is probably the way to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx