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?

  • 1
    I think the idea behind it can be something like 'What would you do in the case of a hypothetical bugfix? Go to every single database and fix every single copy separately?'
    – dezso
    Jul 2, 2012 at 17:52
  • 1
    Administration overhead is irrelevant here because I can update all databases just as easily as a single database Jul 2, 2012 at 17:54
  • And does your developer know about it? :) And probably he/she feels it implements such a basic functionality that it must be in the very core.
    – dezso
    Jul 2, 2012 at 18:08
  • Yes. That is my question, should something that could be considered core be part of the user database or shared among all databases from a single common database? Jul 2, 2012 at 18:10
  • Yes, I tried to present two more or less valid arguments in favour of centralization. I think it can be rather a question of policy or your personal taste - it is clear from the wording that you dislike the idea.
    – dezso
    Jul 2, 2012 at 18:35

3 Answers 3


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:

  1. there is only one copy of the object to maintain
  2. the developer doesn't have to provide three- or four-part names


USE UtilityDB;
CREATE FUNCTION dbo.LastIndexOf(...) ...
USE otherDB;
CREATE SYNONYM dbo.LastIndexOf FOR UtilityDB.dbo.LastIndexOf;

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.

  • Let me provide a rebuttal, since you seemed to want one and now I have a few moments to spare that I didn't have earlier. :-) (1) I assumed that the set of databases in question were all related, and are not parts of completely different applications. (2) In this case I don't suspect there is much danger for a function called LastIndexOf to change, much less in a way that breaks only some of the apps/databases. I'd say it's a bigger problem for CLR or truly central/complex functions that service multiple apps. (3) My actual preference would be to not use a function at all for simple tasks. Jul 2, 2012 at 23:15
  • 1
    Always welcome your thoughts of course :) Jul 2, 2012 at 23:40
  • Hey there. As the creator of SQL# I do appreciate the positive mention :-). However, I should clarify that the main decision point for using a separate Schema for all SQL# objects was to create a name-space so that there would not be naming conflicts no matter where SQL# was installed. Still, you do bring up a point that needs to be considered regarding versioning, depending on how the app is structured. Personally, I like having a Utility DB for commonly called library stuff, but it does increase the risk a little and requires thorough testing. Jan 13, 2016 at 20:37

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:

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.