March 10, 2004 at 8:18 am
I'd like to solicit comments regarding where to store objects such as user-defined functions that I want to use in most, if not all, of my databases. I see three options:
1. Store one copy in master.
2. Store a separate copy in each database. Maintenance becomes a problem.
3. Create a special database (say, called OBJECTS) solely for these type of objects.
What do you think?
Thanks,
Mike
March 15, 2004 at 8:00 am
This was removed by the editor as SPAM
March 15, 2004 at 10:03 am
We've gone with option #3, not only for user-defined functions, but also for tables and views that are used by multiple applications. We've found that it's easier to maintain than storing copies in each database and we don't clutter the Master db with non-system objects.
Remember that you must allow cross-database ownership chaining to make this scheme work.
Greg
Greg
March 15, 2004 at 11:53 am
Thanks, Greg. That's the direction we're leaning, and the reasons you provided for doing so are good ones.
March 16, 2004 at 12:26 pm
I have a question about option #3.
I can understand having shared tables in a shared object database, it's fine to reference them there in code. However, suppose you have generic stored procedures in the shared object database. It's fine to reference them there in code, but how would you make those stored procedures execute in the context of the database they are called from without making them heavily dependent on dynamic SQL? Such behavior is inherent with option #2 and is provided automatically by option #1.
March 17, 2004 at 9:25 am
We don't have stored procedures in the "common object" database. If an application needs to access the common tables or views, the stored procedure is kept in the application's database not in the "common object" database.
Greg
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply