Where to put shared Code? Whats your opinion?

Question

Where to put shared Code? Whats your opinion?

Richard McSharry

Ten Centuries

Points: 1249
More actions
July 9, 2009 at 6:51 am

#223428

Hi,
What is the recommended practice for UDFs that are used by several databases? Is there any reason why I should not create a 'Code' database that stores all the UDFs my team needs, rather than creating copies of these bits of code in every database that needs them.
What do you do? What is MS recommeneded practice?
Thanks,
Richard

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

at our shop, we stick useful udf's and procedsures in the model database; everyone(well developers) knows where they are if they need to copy them to a newly restored database, and new databases inherit them automatically.

they are also of course in Team Foundation Server as well, but for daily utilitarian purposes the are there.

we also have a set of scripts, that assumes or creates a "dbversion" table , and a lot of if not exists() CREATE scripts so you can run it against a blank database and insert the required/missing items.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 2

I use a database called "Common" for that kind of thing. Keep a Numbers table and a calendar table in there as well as any procs or UDFs that might have multi-database use. For one shop, kept a table of US Zip codes in there too, since pretty much every database there needed that.

Since I keep tables in it, I don't put them in model. No need to take up the disk space for that. Instead, with 2005/2008, I create synonyms in model for those tables, and keep the tables themselves in Common.

I also don't like keeping code like that in model, because if I decide to refactor it, or add to the library (new proc/UDF), I would then have to publish that to every database. Keeping it in Common means I only have to edit it once per server.

I set the security on Common so anyone can read the tables or execute the procs/UDFs, by adding those abilities to the public role. Updates, object creation/change, etc., are limited, access is pretty much open. (That does not include access to the system functions/views/tables/whatever, just the ones that are for common use.)

It works quite well.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I agree with GSquared in that I would put them in a Common/Utilities database and make them available via the public role. I never considered putting synonyms to them in model though, which is a good idea so that they are available in any new db's.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Richard McSharry Ten Centuries Points: 1249 More actions · Answer 4

Thanks for the replies, makes perfect sense to do it like that.

😀

R.