Data Access Layer - Design advice on replacing UDFs
I am currently trying to get my database devs to embrace set-based programming and am assisting in the redesign of our data access layer. In particular, I want to remove our dependance on UDFs with regard to their RBAR-ness. I am not too sure what the best practice is in this respect. Quite a few of the UDFs are there to "translate" an Id to a human-readable value, or to build a string depending upon certain criteria. Am I right in thinking I should move the queries from the UDFs into a view that has this the same logic but expressed in a set-based manner? I have tested my assumptions thus far replacing the UDFs with a CTE that does the same work and seen huge performance boosts. I know I will have to bring clear arguments with this, as the devs have a poor opinion of views, especially when joining them with other large data-sets (these opinions are not necessarily correct). How do you accomplish this sort of thing in your environments?
I think your approach is certainly valid. The other way to approach it, which might get more traction is to replace the view idea with an inline table function and cross apply. Thereby keeping the functional approach, but also getting out the RBAR factor. I mean, let's face it, what you write is smart and well considered. I would trust you to come and give me advice on my databases (I always come from the approach that another pair of eyes is worth a huge amount). So I think your problem isn't one of knowing what to do, but of persuading the dev's that you're dealing with of that fact. I usually find that the way to win over a developer is to show them a solution that urinates on the performance of the existing solution from a great height. I think you'll probably have to dig into why they think views are bad - for example, were they once described by someone as a 'magic bullet' and then over-used without realising that the query optimizer would still need well optimised indexes on the underlying tables? (I've seen that a few times).
I would have gone for the table function as my first alternative to build a comparison, over the CTE. I would guess that as it sounds like a big rebuild project that you may find instances where one is better than the other and you will end up with a mix of CTEs and TVFs. It may help your 'sell' to the devs that you have bth of these as alternatives and **they** can discover which is the better option while they develop. Get them to see the speed that set based solution can apply and select the better of the two.