問題描述

我一直在開發一些存儲過程，并且我一直在重復一部分代碼，這些代碼根據其他幾列派生出一列.因此，我沒有將這段代碼從一個存儲過程復制到另一個存儲過程，而是考慮使用一個函數來獲取輸入列并生成輸出列.

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函數從多列返回值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！