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.
--Jeff Moden
Change is inevitable... Change for the better is not.