It depends on the operation. The heavier the script function, the less the overhead. But even for small script functions, the overhead isn't that big.
Consider the following example query:
FUNCTION F(value INT32) INT32 AS StringLength(CAST(value AS NVARCHAR)) END;
SELECT Sum(F(value)) INTO t_2 FROM CALL ValueSequence(1, 1000000, 1);
The operation (F) is tiny, that emphasizes the overhead heavily. We just convert an integer value to a string and then measure the length of the string, and do this for a million numbers.
I created two other versions of it separating the function into a script in C# or VBScript like this:
FUNCTION F(value INT32) INT32 AS SCRIPT [s4_vbscript];
SELECT Sum(F(value)) INTO t_4 FROM CALL ValueSequence(1, 1000000, 1);
F = Len(CStr(value))
Here are the performance numbers from the log:
Query: [q2_function] (2.578 sec)
Query: [q3_csharp] (3.767 sec)
Query: [q4_vbscript] (3.382 sec)
As you can see, the overhead is not big. (And VBScript apparently won over C#, usually it goes the other way.)
If we inline the function, we get this:
Query: [q1] (1.250 sec)
...so the overhead for calling script functions is comparable to that for calling other query constructs.
See attached MXB.