50

I have a user defined function:

create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
  -- blah blah
end

Now I want to join this on another table, like so:

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f 
join ut_FooFunc(f.ID, 1) u -- doesn't work
where f.SomeCriterion = 1

In other words, for all Foo records where SomeCriterion is 1, I want to see the Foo ID and Desc, alongside the values of Field1 and Field2 that are returned from ut_FooFunc for an input of Foo.ID.

What's the syntax to do this?

81

You need CROSS APPLY not join.

The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table.

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f 
Cross apply ut_FooFunc(f.ID, 1) u
where f.SomeCriterion = ...
0

I know the thread is old, I was asked the same question, I did a test, the result as follows...

Records in FacCurrencyRate = 14264 while TestFunction returns 105 if executed independently.

    SELECT F.*, x.CurrencyKey, x.CurrencyName
    FROM ( 
           SELECT CurrencyKey, CurrencyName FROM dbo.TestFunction()
        ) x
    INNER JOIN [dbo].[FactCurrencyRate] F ON x.CurrencyKey = f.CurrencyKey;

The execution time is...

    (14264 rows affected)
    Table 'FactCurrencyRate'. Scan count 1, logical reads 75, physical reads 1, read-ahead reads 73, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCurrency'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 749 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

If I use the suggested answer as follows...

select F.*, x.CurrencyKey, x.CurrencyName from [dbo].[FactCurrencyRate] F
cross apply dbo.TestFunction() x

The execution time and result count is...

(1497720 rows affected)
Table 'FactCurrencyRate'. Scan count 1, logical reads 75, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 38110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCurrency'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2106 ms,  elapsed time = 43242 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

What I see here is that inner query brings out more correct set of results and execution time is much more efficient. Do correct me with better approach to accomplish the same!

  • 1
    The cross apply is applied to each outer row (so row by row), this is why it is so much slower, especially on larger datasets. By reversing it (because you only want results that have a match in the TVF then you greatly reduce the exectuion time (no calls that do nothing) as well as how many rows you get returned. But your two statements are not equivalent as the second returns far more rows than the first. As far as correctness goes, that depends on your business requirements. – Jonathan Fite Apr 18 at 12:17
  • Yes, I agree. However, I wrote the code in light of initial question where I assumed that there is a one to many relations between the tables. And, secondly, I was asked a question in an interview. Thanks – user3104116 Apr 19 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.