sql server (alternate): I know that a query inside a called UDF does not show up when displaying the
estimated query plan, but are the resources (CPU, DiskIO) used by a UDF that
is call from within an SProc or embedded in a SQL statement included in
sp_who2 and Profiler BatchCompleted?

Also how performance draining is it to do something like the following. I
assume for every row returned the udf will be called causing 8000 table
scans on DateTable?

Create Functionu udf_GetCountDates (@StartDate datetime, @EndDate datetime)
Returns Int
begin
Return(
select count(*) from DateTable
where ColDate > @TestDate and Col1Date <
)
end

--DateTable has 5,000 rows

Create proc Test
@TestDate datetime
As
select Col1, Col2, udf_GetCountDates(@TestDate, Col2)
From OTable

--OTable has 8,000 rows
Go

Thanks,
Ray

Ray (someone@nowhere.com) writes:
[quoted text, click to view]

I think so, but I am not going to swear on it.

[quoted text, click to view]

Yes. So would a direct subquery in the SELECT list to, but there is
quite some overhead for a scalar UDF in SQL 2000. Generally be careful
with scalar UDF, and benchmark before using them. There can be drastic
performance penalty, as the UDF more or less transform the query into
a cursor internally.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at