Calling UDFs in master database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Calling UDFs in master database

Hello.
Actually I’m using an UDF to retrive a simple string value. So in my procs I have some code like:
declare @Ret varchar(30)
select @Ret = dbo.myFunc(parameter1) But some days ago, I started using the same myFunc in various databases, and I’m thinking put the myFunc in master database (because I don’t want to have too myFunc copies), and I’m worried in what kind of performance problems can I get with this.
The function is too simple (just some select case). My worry is: in my database procs, if I call select @Ret = master.dbo.myFunc, will I get problems like my proc cache plan disappears from proc cache, because I calling a function in another database? Best Regards.
Esio Nunes
There is one cache per instance of SQL Server, and AFAIK it doesn’t relate to specific databases. You may be thinking of the system stored procedures in the master database, which by magic can be invoked on objects inside your database. A quick test shows me that the same magic does not extend to user defined stored procedures and functions. So unless you include the db name as part of the object references in the function definition, a function can only reference objects that exist in the same database. And you cannot use dynamic SQL in a UDF, so you have little or no flexibility. You can of course use dynamic SQL in a stored procedure. I would create an additional database for functions like this, rather than overloading master. It’s also easier to restore a non-system database in case of a problem. Another option would be to add the UDF to the Model system database, which is the template for all new databases created on this instance of SQL Server. This way, the new UDF would be included with any new database you create.
In SQL Server 2000, there is a hack. But it is unsupported, so I am not going to put it here. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Roji, would that involve an SP in master that scripts a temp SP that includes the database references?
No. FWIW, it’s also still available in 2K5 as Frank and I were discussing on a thread somewhere else here…
Here is it, anyways. http://groups.google.com/group/micr…ystem function"+"UDF"&rnum=1#61b4431f26f353a2
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Why go to so much trouble just so you can call it as a UDF? A sproc with dynamic SQL would be easier to set up.
A procedure may not be a direact replacement for a UDf always. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
You can always insert the resultset of the proc into a table variable …
]]>