April 8, 2008 at 6:52 pm
Hi,
I need to manage some 100 databases where there is a growing need of some common functions and stored procedures being there automatically for any newly created database.
Where can I add those functions and stored procedures so they appear everywhere?
In my research I have found that every database is created using the "MODEL" database as a reference so if I modify that database to add those functions and procedures then in theory they should appear everywhere correct?
What happens in a situation where I have to modify any one of that function or procedure? Would a change in the MODEL database replicate along all the databases?
Is there a way I can make those functions and procedures not editable or dropable?
WRACK
CodeLake
April 8, 2008 at 8:22 pm
WRACK (4/8/2008)
Hi,Where can I add those functions and stored procedures so they appear everywhere?
In my research I have found that every database is created using the "MODEL" database as a reference so if I modify that database to add those functions and procedures then in theory they should appear everywhere correct?
Yes, the model database.
What happens in a situation where I have to modify any one of that function or procedure? Would a change in the MODEL database replicate along all the databases?
The changes in model DB are not propogated. You'll need to do that manually. You might look into RedGate's tools like SQL Compare.
Is there a way I can make those functions and procedures not editable or dropable?
That would only be by applying appropriate permissions to them.
I hope this helps.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 8, 2008 at 8:57 pm
Model database is what is used as the model for creating NEW databases. So - whatever objects exist in Model at the time of creating the new database would be copied into the new database.
It's a one-shot thing though. You'd have to copy those things to each of the user databases.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 8, 2008 at 8:59 pm
Cool thanks Jason.
I will start adding them to the Model database.
As for the changes I will have to write some sort of script to propagate them to all the databases.
And for users not being able to edit or delete them, I have to talk to the website administrator and database administrator together to find a workable solution.
Thanks again.
WRACK
CodeLake
April 8, 2008 at 9:01 pm
Thanks Matt,
Only thing confused me was your reply
You'd have to copy those things to each of the user databases.
I assume you mean if I add them to Model database now then it will be applicable to new databases but not for the existing database where I have to add them manually. Am I right?
WRACK
CodeLake
April 8, 2008 at 9:54 pm
WRACK (4/8/2008)
Thanks Matt,Only thing confused me was your reply
You'd have to copy those things to each of the user databases.
I assume you mean if I add them to Model database now then it will be applicable to new databases but not for the existing database where I have to add them manually. Am I right?
yup - that's it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 8, 2008 at 10:39 pm
I have created some of the common stored procedures (mainly related to Administration) in master. I have also used the prefix sp_ for them. It is stored ina central place and will be accessed from there. Just another thought.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
April 8, 2008 at 10:56 pm
So let me get this right.
I create a function called Blah in the master database and rather than calling that function if it was in the same database like dbo.Blah(), I would call it as master.dbo.Blah()
Keeping in mind that this are very common functions that are used from almost every procedure in every database, if they are in the master database then would it hurt the database server performance?
WRACK
CodeLake
April 9, 2008 at 4:13 am
I actually put the common stored procedures into master. I have not tried with UDF. One of the reason is you need to refer them as master.dbo. At times its easy to have those functions in each dtabase as it could be backed up easily.
I have no clue on how they are written, do they do and whether they read data from user database or not. IF they access any sort of data from user database, it i beeter to keep them in user database.
No function or stored procedure will execute from disk. They all will be in Memory and will operate from there.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
April 9, 2008 at 7:13 am
WRACK (4/8/2008)
So let me get this right.I create a function called Blah in the master database and rather than calling that function if it was in the same database like dbo.Blah(), I would call it as master.dbo.Blah()
Keeping in mind that this are very common functions that are used from almost every procedure in every database, if they are in the master database then would it hurt the database server performance?
Ah - you're talking scalar scalar functions?
If they're scalar functions requiring no data access, then yes, you could also put them in a utility database (I prefer to throw non-admin stuff in a utility database, since I don't want any of my code possibly interfering with, say, a database restore of Master.)
On the other hand - functions (unless they're CLR) can't access data outside of the database they're created in, so they get to be limited that way. CLR can play tricks to do other things, but its resource management can make functions written in it perform not so well.
Also - I have to say I get nervous when I hear functions being used everywhere. Functions tend to break performance badly, even when you try to use them wisely. Even on simple math functions, you can get a well-tuned query to drop a LOT of its performance simply by replacing a calculation with a function. Using them in WHERE or ON clauses messes with using indexes efficiently.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2008 at 5:55 pm
Thanks Matt.
No I don't have any CLR functions and yes they all are scalar functions. None of them are database dependent.
e.g. I have a function called LCD which calculates a Least Common Denominator (spell check..!)...things like that.
I know about the pitfalls of using functions and especially using them in-line in a query but mostly they are used to assign or calculate some values and store them to a variable once off and then that variable is used in the query rather than using the function in a query.
Cheers 🙂
WRACK
CodeLake
April 10, 2008 at 6:30 pm
WRACK (4/10/2008)
Thanks Matt.No I don't have any CLR functions and yes they all are scalar functions. None of them are database dependent.
e.g. I have a function called LCD which calculates a Least Common Denominator (spell check..!)...things like that.
I know about the pitfalls of using functions and especially using them in-line in a query but mostly they are used to assign or calculate some values and store them to a variable once off and then that variable is used in the query rather than using the function in a query.
Cheers 🙂
Then the utilityDB sounds like the path for you. Just remember you have to call them using the full three-part path (ultilitydb.dbo.lcd())
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply