As far as I know, BOL exmaple on fn_trace_getinfo used to use
:: instead of sys schema in the example like following
From
SELECT * FROM ::fn_trace_getinfo(default)
To
SELECT * FROM sys.fn_trace_getinfo(default)
Are there any differences between those two?
And what does :: mean?
OK, i hope this (UDF starting with fn_...) helps
From the page it seems that :: would tell Sql Server that this has to be a System UDF, and will only be found in Master DB.
Q:
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?
A:
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.
Even the SQL Experts are not sure. It changed from SQL Server 2000 to SQL Server 2005
My random guess is the user/schema separation in SQL Server 2005 removed the need for a shorthand system schema