> 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?

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns93C9E64E91CADYazorman@127.0.0.1...
> John P. Marshall (jmarshall-nospam@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, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp