Functions Database

Question

Functions Database

r5d4

SSCrazy

Points: 2519
More actions
December 1, 2016 at 4:36 am

#332478

Hiya,
I'm on a site where SQL Developers have a functions database which only contains functions.
The functions are used via 3 part naming in code in other databases.
It is done for manageability, are there any performance reasons for not doing so?
(For the purpose of this question,I'm not considering security )
Thanks
Rich

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

r5d4 (12/1/2016)
Hiya,
I'm on a site where SQL Developers have a functions database which only contains functions.
The functions are used via 3 part naming in code in other databases.
It is done for manageability, are there any performance reasons for not doing so?
(For the purpose of this question,I'm not considering security )
Thanks
Rich

Unless someone has "gone crazy" creating massive quantities of really intricate complex functions, it's not likely to take up very much space, so I might be tempted to create a functions database that has one master copy, and that master copy is backed up and restored to every other SQL instance, bar none. That way, things like DelimitedSplit8K, which you can find here:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

are readily available "everywhere", so to speak. That way, no one has to do any cross-server stuff. If the permissions are set up for read/execute access to public, you're probably good to go. I would not grant anyone the right to create new functions in the copy, or for that matter, create anything else in the database either. You might also include a read-only calendar table. If you always have a small team in control of the master copy of this database, and just deploy new stuff by restoring from a backup of your master copy, it's pretty easy to deal with.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

r5d4 (12/1/2016)
are there any performance reasons for not doing so?

There are a lot of performance reasons for not using functions at all (unless they're in-line table), but if you're using them, which database they're in won't make a difference

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 3

- Cross database access requires some additional security checks.

- The connection to another database will require an additional session, which will have its own CPU and memory overhead.

- There is the possibility that an open transaction in "this" database will be affected by a connection to the function database, but I don't think so.

In summary, yes there is some additional overhead. Whether or not it will be significant (unlikely) depends on many other factors specific to your environment and usage.

However, I've never worried about it and have used functions in other databases many times. I've never once had the cross database function be the culprit of a performance problem. You'll almost always run into some other problem first.

Wes
(A solid design is always preferable to a creative workaround)

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

r5d4 (12/1/2016)
Hiya,
I'm on a site where SQL Developers have a functions database which only contains functions.
The functions are used via 3 part naming in code in other databases.
It is done for manageability, are there any performance reasons for not doing so?
(For the purpose of this question,I'm not considering security )
Thanks
Rich

I have a similar setup with a small database that has some common tables, stored procedures, and inline TVF in one database, and then synonyms in all the other databases that need to reference them. As long as all the databases involved are in the same instance you shouldn't notice any performance issues.

I like to use synonyms for cross-database interaction instead of a 3 part name because our nomenclature has the environment name as a suffix of the db name (e.g. _DEV, _QA, _UA, _PROD), so using a synonym makes the code more portable from environment to environment.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

Chris Harshman (12/1/2016)
I like to use synonyms for cross-database interaction instead of a 3 part name because our nomenclature has the environment name as a suffix of the db name (e.g. _DEV, _QA, _UA, _PROD), so using a synonym makes the code more portable from environment to environment.

Exactly spot on, Chris. The standards that we've adopted at work is the ONLY synonyms and the definitions of linked servers can use more than a 2 part naming convention. As you point out, the multi-environment prefixes and even multiple versions of the same database on the same server make it a really good idea to totally avoid anything more than 2 part naming in code. Further, stuff like database name changes happen during migrations, etc, etc. Rather than having to go through all of the code to find and fix 3 part naming, we can just change the synonyms and we're done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27782 More actions · Answer 6

It makes sense to me to have these functions in their own database so as to keep a consistent definition of them in one central spot. Keeps you from having to re-invent the wheel so to speak when you need a function. I believe (though I could be wrong, havent tested this) that if you create a function in the Model database, then this will propagate to any new database that you subsequently create. The difference here is that these , though have a similar genesis, are separate instances of the function and not pointers to the same one. You would have to make these read only obviously to keep them consistent. This is a strategy for in the case you did notice performance issues , though I doubt you would. I think the way it is now is fine.

----------------------------------------------------