February 19, 2008 at 3:14 am
Hi,
I have implemented a bunch of UDFs that were first specific to single databases. But now it turns out that their logic is being used by different databases. They are not specific to a single business domain (in this case I would locate them in the appropriate database) but something like "global": String manipulation, conversion, special aggregation functions...
Where should I put these UDFs? In the master or model database? But looking into SSMS it turns out that the system dbs don't contain a "aggregate function" node as the user dbs do have. Is that a bug or a feature?
Glad to here your suggestions!
Alexander
February 19, 2008 at 4:26 am
In SQL Server there is no common place to put your common functions. Put whatever objects in the same database that is using those objects.
If you put any object in a different database and using that object from another database need a cross db calls which is performance wise not good.
I don’t think it’s a bug in SQL Server. It should be the way that they have designed it.
Susantha
February 19, 2008 at 5:44 am
If you do create a common set of code that you want to access from other databases, I wouldn't put it in master or msdb. You never know when a service pack or hotfix may make changes there that could muck up your life. Those are MS's db's, personally, I'd leave 'em alone as much as possible. I would either create a database that contains it (which includes all the caveats mentioned above) or put it into the model database (which will mean that code gets created in every database you create, but it means that when you need to modify a function, you have to do it in multiple locations). There are trade-offs in either direction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2008 at 6:36 am
Thank you,
as far as I can see, it is not wise to put things like that in the master db.
One issue that still is not clear is performance: As we are talking about spreading code across different dbs while the data stays where it is: Is there really a significant performance loss if a function or a procedure is accessing data from another database?
Alexander
February 19, 2008 at 7:50 am
February 19, 2008 at 9:16 am
I would put the UDFs in the model database, then create a SYNONYM in each of the user databases ("dbname" is the name of such a database in the following example) like this:
USE dbname;
CREATE SYNONYM dbo.UDFname FOR model.dbo.UDFname;
Code needing to refer to the UDF will just need to refer to it as dbo.UDFname.
The nice thing about model is that any newly created databases will inherit the synonym from model without you explicitly creating it as above.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply