locked
Creating Common Server Wide Tables RRS feed

  • Question

  • Hello,

    I find myself commonly creating Calendar Tables, Tally (Numbers Tables), UTC Offset table etc. I commonly join to these tables for sp calculations / purposes. The problem is that I have to create them, use them, then drop them. My thought, and perhaps it is a bad one, is to create custom system tables per database, or better yet create something server wide that would persist and be reliable for my sp's udf's etc. This would save me the performance time and extra code of creating these table objects and dropping them. Add in the real world scenario of an sp using the same sp and the Calendar table getting created 20 times in 60 seconds and you see the effort / reward I'm thinking I will achieve.

    Any thoughts or educational links are greatly appreciated.

    JB

    Wednesday, February 9, 2011 4:30 PM

Answers

  • Not sure I'd use model for this - I might be more tempted to enforce this rule using source control - "every time you create a database you use my script as the starting point".

    I can imagine a scenario where you have to deploy to a new server where you don't know what's in model - and unless you are deploying via backup/restore of your dev environment you won't get the "template" tables.

    Not saying it wouldn't work though, just that I've never seen it done this way - how often do your developers get to use "create database" in any case? Around here it would be only once, at the start of a "greenfield" project, and these are as rare as hen's teeth.


    Twitter
    Thursday, February 10, 2011 4:01 PM

All replies

  • It makes sense to standardize and automate.

    You can just collect them all into a script which you save in a .sql file.

    You can execute the script into designated databases with SQLCMD -i option.

     


    Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM
    Thursday, February 10, 2011 3:32 PM
  • I understand I could do that. I was thinking more along the lines of putting them in the model db. Then you would have them instantly in every db that gets created. You could also have common functions / sp's in the model as well. For example a Calendar table spanning from 01-01-1901 through 3001 with holidays, day of week, Quarter etc. would have some standards uses. So you might also put in the model an sp ReturnReocurringEvent. You pass in some parms such as 3rd Tuesday and it would give you every third Tuesday between the your start and end date. I've done it on my dev box and the "concept" looks really inviting *BUT* is it a good idea to use the model db in this way...and is it a good idea to have such a policy. In effect we would come down from on high and tell developers you need to use these set based approaches and tables.

    So your idea would work great except I'm thinking all or nothing. When I got mgmt and say I want to make this the law of the land i want to have my case air tight.

     

    JB

    Thursday, February 10, 2011 3:43 PM
  • Not sure I'd use model for this - I might be more tempted to enforce this rule using source control - "every time you create a database you use my script as the starting point".

    I can imagine a scenario where you have to deploy to a new server where you don't know what's in model - and unless you are deploying via backup/restore of your dev environment you won't get the "template" tables.

    Not saying it wouldn't work though, just that I've never seen it done this way - how often do your developers get to use "create database" in any case? Around here it would be only once, at the start of a "greenfield" project, and these are as rare as hen's teeth.


    Twitter
    Thursday, February 10, 2011 4:01 PM
  • Good point. So the dba's would have to ensure that part of their server build would be to run the scripts against model before releasing the server. Hmmmmmm

    Thursday, February 10, 2011 4:26 PM