User-Defined Functions - Are they poor coding practice?

Question

I'm writing reports with fairly complex datasets, lots of joins. To simplify matters, and because I'm basically an OO developer, I've been writing little (usually scalar) functions to do the work that could be done by joining onto a subquery. This sort of thing:

SELECT 
    x.Name, x.userId, 
    ... [more columns and joins]
    dbo.CountOrders(x.userId)
FROM Customers x 
WHERE ...

Is this good practice? Sloppy? Slow? Should I be writing regular T-SQL to do this stuff?

Answer 1

I would pretty much never have a scalar UDF that does data access.

Scalar UDFs can't get expanded out by the optimiser and need to be evaluated RBAR. It is fairly well established that this is not a good idea.

Some example reading.

• TSQL Scalar functions are evil.
• Scalar functions, inlining, and performance: An entertaining title for a boring post
• Higly Voted Connect Item