Performance evaluation of User defined functions

[Maximus]

Hi
While its known and acknowledge that stored procedures greatly help in increasing the speed of our database queries, i am unaware of the benefits of user defined functions. In a current project, i have used them quite a lot espically in stored procedures.

For example,
if i hav a table Users with
UserID int,
Username nvarchar(50),
one of the functions i made was fn_GetUserNameFromUserID(@UserID)
which returns the name of the person,

so instead of long joins to get usernames, i wud easily be able to get the name of a person if userid is another table say X.
so i could so select fn_GetUsernamefromUserid(X.UserID).

While i do realize that a simple join is nice, but sometime when a query involoves 3 + tables, it makes life simopler. CAn anyone tell me about the performance overhead of this, or is this the proper approach to using user defined functions.

Thanks

[rmiao]

You can put query with function and query with stored procedure call in same batch and compare their cost in execution plan.

[skhanal]

If you are using a UDF in a SELECT you have to be aware that the function is executed for each row of the result set which may make the query very slow. If you are using to get just one row back then whether you use SP or UDF, it may perform equally well.

[Maximus]

HI skanal, thanks for the info.
Check
http://forums.databasejournal.com/sh...ad.php?t=39791

for a really wierd anomaly which i am seeing