Sql-server – Create function in central database or repeat in each database

ddlfunctionsperformancesql server

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?

Best Answer

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.)