471,073 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

UDF starting with fn_...

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

Jul 20 '05 #1
2 4590
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
Jul 20 '05 #2
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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Andrew Bullock | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.