UDF Used In SubQuery: Is It Called At EACH Row?

Hi,

When a column is evaluated against an UDF in a SELECT ... or WHERE ... It makes sense that the UDF is called for every row of the SELECT. But is it still true if the UDF is called in a subquery as below?





Code Snippet

SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
I've made a test and the SQL Profiler Trace shows that the UDF is called only once.

Can anyone confirm if my test is valid and most importantly that the UDF is called only once? FYI, I never use sub queries. This is to clarify a technical detail for our performance investigation.

Thank in advance for any help.


Here is the code to setup the test:
USE NorthWind
GO

CREATE TABLE dbo.UdfTest (
LineID int Identity(1,1) NOT NULL,
AnyText varchar(200) COLLATE database_default NOT NULL
)
GO

INSERT dbo.UdfTest (AnyText) VALUES ('Test1')
INSERT dbo.UdfTest (AnyText) VALUES ('Test2')
INSERT dbo.UdfTest (AnyText) VALUES ('Test3')
GO

CREATE FUNCTION dbo.F_Bogus (
)
RETURNS @tab TABLE (
LineID int NOT NULL,
AnyText varchar(100) COLLATE database_default NOT NULL)
AS
BEGIN
INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1')
INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2')
INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3')
INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4')
INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4')

RETURN
END
GO

Here is the capture of SQL Profiler when executing the statement:
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())

SQL:BatchStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQLtmtStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SPtarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus RETURN 51 2008-05-06 17:58:31.577
SQLtmtCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQL:BatchCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543