The place where I've found functions useful is where you have to join to them. It's not so easy joining to a proc.
Also, multi-select functions use a table variable, which means they don't store statistics, which slows them down, but inline table-value functions can use statistics in the same manner as views and procs (no table variable).
Functions do take a performance hit, but it can be worth it for complex code that will be re-used in many places, if you can minimize the performance issues. I have a hierarchy function (inline, not multi-select) that has very good performance (complex hierarchies in under 20 milliseconds). Placing the same code in each query that uses it makes it about 5-10% faster, but also makes for slower development, more complex troubleshooting, and so on. In this case, the cost is worth it to me. Especially as it only gets called when the nested sets hierarchy is out of synch (which isn't very often).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon