Computed Column Specification

[Dot Net Daddy]

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.

[Hugo Kornelis]

Re: Computed Column Specification

On 10 Sep 2006 14:41:16 -0700, Dot Net Daddy wrote:

>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:

>computed_colum n_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

[Erland Sommarskog]

Re: Computed Column Specification

Hugo Kornelis (hugo@perFact.R EMOVETHIS.info. INVALID) writes:

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx