SQL Select fields of table value function

Question

I have a table valued function declared so that I can return several values in one go. The values are calculated using DECLARE and Maths and Date functions.

The function is structured such that it only takes a 'logged date', and a priority for issues in a support system. I honestly thought that I'd be able to select as follows:

SELECT SupportCall.*, dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority).* FROM SupportCall

I've actually ended up with:

SELECT SupportCall.*,
    SLADays = (select SLADays from dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority)),
    SLARecieved = (select SLAReceived from dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority)),
    SLATarget = (select SLATarget from dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority)),
    SLAHoursRemaining = (select SLAHoursRemaining from dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority))
From SupportCall

I can't see a possible join for an Apply (which I don't fully understand anyway).

Does anybody know whether the function calls with the same parameters will be executed once? If I'm not going to end up with lots of subqueries and function calls when the query runs, then I don't care, the code is actually quite tidy if not concise.

If there is a massive overhead, does anybody know how to select all columns from a table function of this kind (i.e. no keys, just several calculations on the same input data).

Thanks.

Mark

Does `SELECT SupportCall.*, SLAStatus.* FROM SupportCall CROSS APPLY dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority) AS SLAStatus` work? — Jeroen Mostert, Feb 17 '15 at 13:06

score 2 · Answer 1 · answered Feb 17 '15 at 13:07

Don't do that! Inline queries are NEVER faster than JOINS or APPLY. Rewrite your query and check the IO. You can rewrite it something like:

SELECT SupportCall.*,
    SLADays = gs.SLADays,
    SLAReceived = gs.SLAReceived,
    ...
From SupportCall sc
CROSS APPLY dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority) gs

score 0 · Accepted Answer · answered Feb 17 '15 at 13:08

0

Can't you just do this:

SELECT C.*,
    F.SLADays,
    F.SLAReceived,
    F.SLATarget,
    F.SLAHoursRemaining
From 
    SupportCall C
    cross apply dbo.GetSLAStatus(C.createDate, C.priority) F

I hope you're function is an inline function (e.g has a single statement that starts returns table return (...) and does not have a defined result table)

answered Feb 17 '15 at 13:08

James Z

12,209
10
24
44

And inline because it's much much faster, other UDFs are handled row-by-row for each of the rows in SupportCall table – James Z Feb 17 '15 at 13:08
Will this just evaluate GetSLAStatus once for each SupportCall record? I'd hate for the db to generate weird permutations. – Mark Rabjohn Feb 17 '15 at 14:41
Yes this will call the function once per row, but depending on the amount of data you have, that might be really slow too if it's not an inline function. – James Z Feb 17 '15 at 14:46
I'm not too hot with T-SQL. This isn't an inline function - it's a whole raft of DECLAREs plus Insert @returntable SELECT ... RETURN syntax - is that *real* slow? – Mark Rabjohn Feb 17 '15 at 15:01
That will be executed separately for every row in your result set, so usually it's really bad compared to set based things. The bast way to see the impact is plan cache (sys.dm_exec_query_stats + sys.dm_exec_sql_text). – James Z Feb 17 '15 at 16:16

SQL Select fields of table value function

2 Answers2