Jeff Moden (3/3/2011)
Telarian (3/3/2011)
Jeff Moden (3/3/2011)
Oh, be careful now. "It Depends". 🙂 Scalar and Multi-Line Table Valued Functions (mTVF) can very quickly turn code into some pretty nasty, performance challenged RBAR especially if it refers to a table. Think of it as a hidden cursor.
Inline Table Valued Functions (iTVF), however, can be VERY useful and are calculated in the execution plan as if they were a "programmable view". Oddly enough, they also work very effectively to return single values like a Scalar function would when used with CROSS APPLY (thanks to Paul White for that nice little trick).
Don't get me wrong, I'm not saying there aren't usually better ways to do things. But sometimes you end up in less than ideal places and at those times a sub would be good.
Heh... agreed but I was talking about your recommendation to use UDF's. My suggestion is that Scalar and mTVF's can actually put you in one of those less than ideal places and that, with only a few exceptions, iTVF's are a better way to go.
I try to avoid scalar functions. One of the worst performance anti-patterns I've seen are when a UDF contains an inline select for a scalar value. This is a common example where joining the customer table, a view, or TVF would be much more appropriate:
select
app.schedule_date,
app.cust_id,
dbo.get_cust_fullname( cust_id ) cust_name, -- ouch!
dbo.get_cust_phone( cust_id ) cust_phone -- ouch!
from appointments app
where app.schedule_date >= '2011-02-01';
Even for for things like calculated indicators, formatting, or concatenating a customer's first name, last name, and title into a full name, I'll implement those as columns in a view based on the table that contains the columns.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho