Posted by Gert-Jan Strik on 09/20/07 19:10

If you need a UDF to be able to join to another table, then you have not
designed your schema properly. An ID does not come out of thin air. If
you have a relation between tables, you should register the foreign key
properly. So not only is it a bad idea to do this from a performance
point of view, but also from a design point of view.

If dbo.getStatus does a lookup in a related table, then you could simply
join with that table. If you don't want to write such an extra join, you
can move it to a view and select from the view (instead of the table).

--
Gert-Jan


mcleana@sympatico.ca wrote:
>
> I have a view that contains a complex query. A few of the columns
> call a function that returns a specific output. I also use a function
> to do a join as well.
>
> For example:
>
> SELECT l.ID, dbo.getStatus(l.ID) AS statusID
> FROM tableName A
> LEFT OUTER JOIN dbo.Status_LKP s ON dbo.getStatus(l.Leg_ID) =
> s.statusID
>
> For 800 records, this query takes about 9 seconds. I realize that for
> each record, the function is executed on a per row basis, so I am
> looking for alternatives.
>
> Does anyone know of other ways to accomplish something of the same?
> Basically I would like to include UDFs in a query and use those UDFs
> in the where and join clauses.
>
> Thanks

[Back to original message]