UDF starting with fn_...

[John P. Marshall]

I've read frequently how stored procedures we create shouldn't be named with
sp_ since SQL Server will first check the master db before the current db
when trying to find the sp to execute. I always nodded and thought how I
was glad I hadn't done that. For some reason it never clicked that UDF
behavior could be the same.

I have several UDFs that start fn_... When I reference a UDF as dbo.fn_...
does SQL Server check the master db for these and only upon not finding them
check the current db? If so, is there a performance hit for this?

TIA,
John

[Erland Sommarskog]

John P. Marshall (jm**************@isassoc.com) writes:

I've read frequently how stored procedures we create shouldn't be named
with sp_ since SQL Server will first check the master db before the
current db when trying to find the sp to execute. I always nodded and
thought how I was glad I hadn't done that. For some reason it never
clicked that UDF behavior could be the same.

I have several UDFs that start fn_... When I reference a UDF as
dbo.fn_... does SQL Server check the master db for these and only upon
not finding them check the current db? If so, is there a performance
hit for this?

No. To call a system-supplied UDF, you need to use ::, so that is
what SQL Server looks for. All system-supplied UDFs are table functions,
as scalar system functions are not UDFs at all.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John P. Marshall]

Erland,

Cool... thanks for the good news.

Regards,
John

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

John P. Marshall (jm**************@isassoc.com) writes:

I've read frequently how stored procedures we create shouldn't be named
with sp_ since SQL Server will first check the master db before the
current db when trying to find the sp to execute. I always nodded and
thought how I was glad I hadn't done that. For some reason it never
clicked that UDF behavior could be the same.

I have several UDFs that start fn_... When I reference a UDF as
dbo.fn_... does SQL Server check the master db for these and only upon
not finding them check the current db? If so, is there a performance
hit for this?

No. To call a system-supplied UDF, you need to use ::, so that is
what SQL Server looks for. All system-supplied UDFs are table functions,
as scalar system functions are not UDFs at all.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp