|
|
Posted by Erland Sommarskog on 09/10/06 22:39
Hugo Kornelis (hugo@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, esquel@sommarskog.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
Navigation:
[Reply to this message]
|