SQL函数从多列返回值 [英] SQL Function to return value from multiple columns

问题描述

我一直在开发一些存储过程，并且我一直在重复一部分代码，这些代码根据其他几列派生出一列.因此，我没有将这段代码从一个存储过程复制到另一个存储过程，而是考虑使用一个函数来获取输入列并生成输出列.

I've been developing a few stored procedure and I have been repeating a portion of codes that derives a column based on a few other columns. So instead of copy this piece of code from one stored procedure to another, I'm thinking of having a function that takes the input columns and produces the output columns.

基本上，函数如下:

SELECT columnA, columnB, columnC, myFunction(columnA, columnB) as columnD FROM myTable

如我们所见，此函数将 A 列和 B 列作为输入，然后返回 D 列.

As we can see, this function will take column A and column B as inputs, then return column D.

但是，根据一些研究，使用这样的 UDF(用户定义)函数时似乎存在一些性能问题.真的吗?处理这种情况的最佳方法是什么?

However, based on some research, it seems to have some performance issues when using UDF (user-defined) function like this. Is that true? What's the best way to handle this situation?

谢谢各位.

推荐答案

标量函数和多语句表值用户定义函数会导致性能问题，因为它们隐式地将基于集合的操作转换为基于游标的操作.

Scalar functions and multi statement table valued user defined functions can cause performance issues, because they implicitly turn your set based operation into a cursor based operation.

然而，内联表值的用户定义函数不会遇到这个问题.他们很快.

However, inline table valued user defined functions do not suffer from this problem. They're fast.

区别在于您如何声明函数，以及它们内部的代码是什么样的.多语句函数执行它在罐头上所说的 - 它允许您有多个语句.像这样:

The difference is how you declare the fuction, and what the code looks like inside them. A multi statement function does what it says on the tin - it lets you have multiple statements. Like this:

create function slow() returns @t table(j int, k int) as 
begin
    declare @j int = 1; -- statement 1
    declare @k int = 2; -- statement 2
    insert @t values (@j, @k); -- statement 3
    return; -- statement 4
end

内联表值函数不会返回填充在函数内部的命名表.它返回一个选择语句:

An inline table valued function does not return a named table which is populated inside the function. It returns a select statement:

create function quick() returns table as
return
(
    select j = 1, k = 2
);

内联表值函数可以内联"；进入外部 select 语句，与视图的方式大致相同.当然，区别在于 UDF 可以接受参数，而视图不能.

The inline table valued function can be "inlined" into the outer select statement, in much the same way as a view. The difference, of course, being that the UDF can take parameters, whereas a view cannot.

您还必须以不同的方式使用它们.使用交叉应用:

You also have to use them differently. Use cross apply:

select       t.columnA, t.columnB, u.j, u.k
from         MyTable t
cross apply  quick(t.columnA, t.columnB) u

如果不清楚 - 是的，在您的情况下，您只需要一个标量"返回值，但这只是一个表值函数，它返回单列和单行.因此，与其编写标量函数，不如编写一个执行相同工作的内联表值函数，然后交叉应用它.

In case it's not clear - yes, in your case you only want a "scalar" value back, but that's just a table valued function which returns a single column and a single row. So instead of writing a scalar function, write an inline table valued function that does the same job, and cross apply it.

这篇关于SQL函数从多列返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！