Create function in central database or repeat in each 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:
- there is only one copy of the object to maintain
- the developer doesn't have to provide three- or four-part names
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.