Is it okay to use table look up functionalities in scalar function?

Question

In our case we have some business logic that looks into several tables in a certain order, so that the first non null value from one table is used. While the look up is not hard, but it does take several lines of SQL code to accomplish. I have read about scalar valued functions in SQL Server, but don't know if the re-compliation issue affects me enough to do it in a less convenient way.

So what's the general rule of thumb?

Would you rather have something like

select id, udfGetFirstNonNull(id), from mytable

Or is table-valued functions any better than scalar?

select id, 
  (select firstNonNull from udfGetFirstNonNull(id)) as firstNonNull
from myTable

Answer 1

Your first query is fine. One place I work for is absolutely obsessed with speed and optimization, and they use UDF's heavily in this way.

Answer 2

I think for readibility and maintainability, I would prefer to use the scalar function, as that is what it is returning.

Answer 3

The scalar udf will look up for each row in myTable which can run exponentially longer as data increases. Effectively you have a CURSOR. If you have a few rows, it won't matter of course.

I do the same myself where I don't expect a lot of rows (more than a few hundred).

However, I would consider a table value function where I've placed "foo" here. "foo" could also be a CTE in a UDF too (not tested):

select id, 
  (select firstNonNull from udfGetFirstNonNull(id)) as firstNonNull
 from
    myTable M
    JOIN
    (SELECT value, id as firstNonNull
     FROM OtherTable
     WHERE value IS NOT NULL
     GROUP BY id
     ORDER BY value) foo ON M.id = foo.id