Post reply

Where to put shared Code? Whats your opinion?

  • 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

  • July 9, 2009 at 7:12 am

    #1022334

    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!

  • July 9, 2009 at 7:30 am

    #1022349

    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

  • July 9, 2009 at 7:36 am

    #1022352

    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

  • July 9, 2009 at 11:43 am

    #1022575

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

    😀

    R.

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

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