問題描述

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

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.

基本上，函數(shù)如下:

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

如我們所見，此函數(shù)將 A 列和 B 列作為輸入，然后返回 D 列.

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

但是，根據(jù)一些研究，使用這樣的 UDF(用戶定義)函數(shù)時似乎存在一些性能問題.真的嗎?處理這種情況的最佳方法是什么?

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?

謝謝各位.

推薦答案

標(biāo)量函數(shù)和多語句表值用戶定義函數(shù)會導(dǎo)致性能問題，因為它們隱式地將基于集合的操作轉(zhuǎn)換為基于游標(biāo)的操作.

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.

然而，內(nèi)聯(lián)表值的用戶定義函數(shù)不會遇到這個問題.他們很快.

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

區(qū)別在于您如何聲明函數(shù)，以及它們內(nèi)部的代碼是什么樣的.多語句函數(shù)執(zhí)行它在罐頭上所說的 - 它允許您有多個語句.像這樣:

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

內(nèi)聯(lián)表值函數(shù)不會返回填充在函數(shù)內(nèi)部的命名表.它返回一個選擇語句:

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
);

內(nèi)聯(lián)表值函數(shù)可以內(nèi)聯(lián)"；進(jìn)入外部 select 語句，與視圖的方式大致相同.當(dāng)然，區(qū)別在于 UDF 可以接受參數(shù)，而視圖不能.

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.

您還必須以不同的方式使用它們.使用交叉應(yīng)用:

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

如果不清楚 - 是的，在您的情況下，您只需要一個標(biāo)量"返回值，但這只是一個表值函數(shù)，它返回單列和單行.因此，與其編寫標(biāo)量函數(shù)，不如編寫一個執(zhí)行相同工作的內(nèi)聯(lián)表值函數(shù)，然后交叉應(yīng)用它.

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.

這篇關(guān)于SQL函數(shù)從多列返回值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！