Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 '12 at 17:52
    
Administration overhead is irrelevant here because I can update all databases just as easily as a single database –  David George Jul 2 '12 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 '12 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? –  David George Jul 2 '12 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 '12 at 18:35

2 Answers 2

up vote 8 down vote accepted

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

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

share|improve this answer
    
Thanks @Aaron This is more on target for what I was looking for. So what about recompilations/plan cache? Having one object in a utility DB sounds like it's better for performance? Only one procedure/function to compile and smaller footprint in plan cache? –  David George Jul 2 '12 at 20:33
    
@DavidGeorge well it sounds like a function like this doesn't really have data access, and since it's presumably a scalar function, I'm not sure there would really be much benefit at all in terms of plan cache. For a table-valued function that consumed data (say, from some central lookup table), yes you would see some benefit there, but probably more from referencing the same central table than having a single copy of the function. –  Aaron Bertrand Jul 2 '12 at 20:35
    
Yes it is a FN. Thank you for the clarification @Aaron. –  David George Jul 2 '12 at 20:46

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.

share|improve this answer
    
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. –  Aaron Bertrand Jul 2 '12 at 23:15
1  
Always welcome your thoughts of course :) –  Mark Storey-Smith Jul 2 '12 at 23:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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