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]

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

[Erland Sommarskog]

Hugo Kornelis (hu**@perFact.REMOVETHIS.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx