One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database versus putting it in each user database?
The developer was trying to put it in sys schema on his master db so he wouldn't have to qualify calls to it from user databases... sigh
But I wasn't sure what the valid excuse would be to centralize it (obviously not master database) versus each user database?
The way I prefer to do this: put the function in a utility database, and create a synonym to it in each regular database. This way you get the best of both worlds:
e.g.
USE UtilityDB;
GO
CREATE FUNCTION dbo.LastIndexOf(...) ...
GO
USE otherDB;
GO
CREATE SYNONYM dbo.LastIndexOf FOR UtilityDB.dbo.LastIndexOf;
GO
This is especially powerful for CLR functions, since there is extra administrative overhead for changing/deploying those.
And this is way preferable to using master (and marking as a system object, which isn't guaranteed to be forward portable). I'd love to know how your developer expects to create his function in the sys schema, though.
I do understand that maintaining multiple copies of a function in 500 databases is no more difficult really than maintaining a single copy, but having multiple copies is really only a benefit when you do have exceptions (e.g. client A wants their function to handle NULLs differently, or something). In which case I would leave the synonym in all the other databases, and introduce a special version of the function only in that database.
(This assumes that the function doesn't rely on any data access within a client's database, of course - which can certainly complicate matters.)
I'm going to have to disagree with Aaron (and the accepted answer).
Aaron's approach is the way I like to deal with "DBA stuff" e.g. maintenance scripts. I would never want to do this with a library function that would be called by a user database.
Why? You'll be heading for the database equivalent of DLL Hell.
Incompatible versions
...Before Windows 2000, Windows was vulnerable to this because the COM class table was shared across all users and processes. Only one COM object, in one DLL/EXE could be declared as having a specific global COM Class ID on a system. If any program needed to create an instance of that class, it got whatever was the current centrally registered implementation. As a result, an installation of a program that installs a new version of a common object may inadvertently break other programs that were previously installed.
Install .net 4.5 on a server running a .net 2.0 application and what happens? Nothing, you're application continues to use the 2.0 framework. Update your LastIndexOf function on a server hosting 3 applications databases (as part of an upgrade to one of them) and what happens? All three are now using the latest version.
The alternative is the approach adopted by SQL#. This is installed to a schema in each user database, so you can safely upgrade the database for Application-X without risking the stability of Application-Y.
If you're working in a tightly controlled change management environment you'll have no choice, you can't upgrade a shared component without testing all consumers of the component. If you're working somewhere a little more "fast and loose", you're free to take your chances with breaking something unintentionally with a patch/upgrade.
The answer to this question depends on whether we are talking about T-SQL objects or SQLCLR objects. There are different (and more) factors to consider when dealing with SQLCLR objects.
In terms of T-SQL objects, both answers here bring up valid points. As described in @Aaron's answer, central DB can be quite handy, and Synonyms can be used to make it easy to have some DBs point to the shared resource and some use a local resource. But the point brought up in Mark's answer regarding increased risk due to the stronger dependency cannot be ignored.
However, if you are dealing with SQLCLR objects, then you first need to consider all of the points I brought up in the following answer:
