Use UDF...
2011-05-07 10:59 kaure 阅读(295) 评论(0) 编辑 收藏 举报今天一个老程序员突然来了一句话：“sql里面的自定义函数的确不错。。。能够把查询给分开。可以节省时间“，那时我就在想函数的功能真的是用来做这个的么。于是就看了一下MSDN上面关于SQL 里面用户自定义函数的解释，看到这到一段:
When evaluating UDFs it is vital to
consider the balance between performance and maintainability. While UDFs
can reduce the amount of common code, be used as part of a common
function library, can promote shorter code blocks, and are generally
easier to maintain than additional versions of the same SQL logic, it
would be reckless to use a UDF without first taking into consideration
any of the drawbacks.
It
would be a bad idea to use a UDF if performance suffers tremendously.
For example, assume that there is a UDF that performs a SQL SELECT
statement that takes one second to execute. If this UDF is used in a
SELECT or a WHERE clause it will be executed for every row. Thus the
time the main query takes to execute could increase drastically
depending on such factors as the number of rows evaluated and returned
and the types of indices in place. Before using a UDF in this type of
situation, carefully weigh the options and do some performance testing.
However, using a UDF that performs a calculation such as the one shown
in Figure 3 barely affects the performance of query. As
with any tool, when used properly and evaluated accordingly prior to
going live, UDFs offer great convenience and maintainability.
MSDN 上面说得比较清楚的时，如果UDF用在行查询上面。这会让查询的时间变得更长。但是对于计算用的UDF，其实对查询的性能与时间基本没有什么影响。其实这个结果我们也可以想像得到，如果UDF是查询数据库表的，那么循环查询一个表势必会耗费更多的时间。。。。
看来使用这些东西的时候我们还是得思考一下才行。。
