用户定义的函数 - 它们是糟糕的编码实践吗？
我正在编写具有相当复杂的数据集和大量连接的报告。为了简化问题，并且因为我基本上是一个 OO 开发人员，所以我一直在编写一些小（通常是标量）函数来完成可以通过加入子查询来完成的工作。这类事情：
SELECT
x.Name, x.userId,
... [more columns and joins]
dbo.CountOrders(x.userId)
FROM Customers x
WHERE ...
这是好的做法吗？马虎？慢的？我应该编写常规 T-SQL 来完成这些工作吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我几乎永远不会有一个进行数据访问的标量 UDF。
标量 UDF 无法由优化器扩展，需要进行 RBAR 评估。众所周知，这不是一个好主意。
一些示例阅读。
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.
根据我的观点和经验，内联函数不一定是邪恶的。有时我面临着不使用函数就无法完成的任务。如果您在存储过程中一遍又一遍地重用相同的例程，那么就像在古老的 OOP 中一样，您创建一个函数并使用它。但只是为了干净和简短的代码，我不建议使用函数，如果它是一次性的。
性能影响或改进来自表索引和索引维护。只要正确创建和维护索引，内联函数就和编写普通的 sql 语句一样好。
In my opinion and from my experience inline functions are not necessarily evil. Sometimes i face a task that i couldn't achieve without using functions. If you are reusing the same exact routine over and over again in your stored procedures, then, just like in good old OOP you create a function and use it. But just for the sake of clean and short code I would not recommend using functions, if its one time use.
The performance hit or improvement comes from table indexing and index maintenance. As long as the indexes are correctly created and maintained, inline functions are as good as writing plain sql statement.
我不认为在 SQL 中进行函数调用计算有多大危害，只要表的用户清楚这些计算是在哪里进行的。您在这里所做的只是创建子查询的快捷方式。然而，我从来没有对我的主要访问查询执行此操作，因为人们开始“认为这是理所当然的”此类计算。
如果我发现自己重复执行此操作，我会发现更有价值的做法是制作一个以预先计算的形式包含此类信息的后备表，然后使用触发器使其保持最新。一个例子是一个数据库，我将财务摘要数据从发票行项目汇总到报价级别，然后再次汇总到提交级别（有多个报价），然后再次汇总到保单级别（有多个报价）来自多个运营商的树木）。
事实证明，大多数查询确实需要策略级别的数据。通过使用触发器和汇总表，我将一些关键查询的速度提高了两个数量级，因为工作已经完成，而已保存更改的性能却降低了一些无关紧要的量。因此，如果您发现自己做了很多汇总查询，请考虑一种方法来避免这些工作......但对于简单的情况，我认为对函数的内联调用很好。
I don't think there is much harm in doing function call computations in SQL as long as it is clear where they are being done to the users of the table. All you have done here is make a shortcut to a subquery. However, I never do this with my primary access queries as people start to "take for granted" these kinds of computations.
If I find I'm doing this repetitively, I find it more valuable to make a look-aside table that contains this kind of information in a precomputed form and then use triggers to keep it up to date. An example of this is a database where I roll-up the financial summary data from the invoice line-items to the quote level, then again to the submission level (which has multiple quotes) and then again to the policy level (which has multiple trees from multiple carriers).
It turns out that most of the queries really need the data at the policy level. By using triggers and summary tables I sped up some critical queries literally two orders of magnitude simply because the work was already done, while decreasing the performance of a saved change by an inconsequential amount. So if you find yourself doing a lot of summary queries, think about a way to avoid the work... but for simple cases I think inline calls to functions are fine.