Post reply

Best Practices for Scripting in SQL Server

  • September 22, 2011 at 9:58 am

    #243619

    Hi guys!

    I am Build Manager and facing some problems from my DB Develoeprs.

    They dont implement "If Exist" checks in DDL scripts and missed Go statements as well. Pls guide me the best SQL Practices for them.

    Problem: When i deploy the patch on single server with Multiple DBs. may be Human error can be occured due to running of Single script twice ; it may create a big mess.

    Kindly share the best practices for DDL that i should communicate to DB Devs. is there any tool that check best practices in scripts?

    Thanks in advance.

    Waseem Bukhari

  • September 22, 2011 at 10:01 am

    #1385277

    I would always use IF exists in the DDL scripts as it avoids getting into errors or creating a mess in the database.

    There is also an option that you can select in SSMS to include this if you are auto-generating the scripts

  • September 22, 2011 at 10:06 am

    #1385279

    I would create templates for sprocs, views, function and provide them to the DB developers. These templates would include the drop then create as well as the GO statements.

    I would also reject any code that does not fit the spec and make them fix it. It would not take much time before the developers started doing this without prompting because they knew they would end up doing it anyway. And don't take any guff about it. Set policy and then enforce it. The templates help them get it right the first time, the enforcement fixes legacy scripts.

    CEWII

  • September 22, 2011 at 2:08 pm

    #1385427

    Thanks for your motivated replies.

    Can you share a sample Template or Guideline with me.

  • September 22, 2011 at 2:10 pm

    #1385428

    There is also an option that you can select in SSMS to include this if you are auto-generating the scripts

    How? Pls share.

  • September 22, 2011 at 2:12 pm

    #1385430

    By preference for version rollouts I never use 'if exists'. In particular to catch those moments of human error. I don't want to see the same version script rolled twice.

    My one exception is for data population scripts. I'll test to make sure I don't end up duping data.

    To each their own, though.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 22, 2011 at 2:14 pm

    #1385432

    waseem.shahzad 45937 (9/22/2011)

    There is also an option that you can select in SSMS to include this if you are auto-generating the scripts

    How? Pls share.

    Change that to TRUE.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 22, 2011 at 2:29 pm

    #1385448

    we use compound IF EXISTS commands to make sure that a table we KNOW exists....well exists...

    (if it's our version of a database)...

    it eliminates issues where the IF NOT EXISTS...CREATE TABLE command executes, even though they are in the master database instead of the desired database....always scripting for the lowest common denominator of dba skills.

    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!

  • September 22, 2011 at 3:03 pm

    #1385465

    waseem.shahzad 45937 (9/22/2011)

    Thanks for your motivated replies.

    Can you share a sample Template or Guideline with me.

    These templates were created for SQL 2000, I am working on an updated set for SQL 2005+

    http://tech.groups.yahoo.com/group/SQL_Beginners_Help/files/Template%20Scripts/[/url]

    Lowell,

    In the case of tables I NEVER do a drop/create unless it is in an upgrade script and it should never have existed before this point.

    --

    What I don't want to get into is the case where the first version in source control has a CREATE statement but all subsequent versions have ALTER statements. I don't know about you but I don't deploy all the intermediate versions, only the release version..

    Also I NEVER EVER want to use the code on the server as source all my code starts as a script usually from a template.. That code is compiled onto the server. In this case the server always contains a COPY of the SOURCE and is never treated as the source. I should never have to script out my sprocs to put them in source control, I should already have a copy of them.

    CEWII

  • September 22, 2011 at 4:03 pm

    #1385480

    Elliott Whitlow (9/22/2011)

    I would also reject any code that does not fit the spec and make them fix it. It would not take much time before the developers started doing this without prompting because they knew they would end up doing it anyway. And don't take any guff about it. Set policy and then enforce it.

    Amen. I recently had this battle with our dev team except I was pushing for USE statements instead of IF EXISTS. I still have to bounce scripts back from time to time but they've adapted and it has made promoting code much easier for me.

    _____________________________________________________________________
    - Nate

    @nate_hughes

  • September 22, 2011 at 4:11 pm

    #1385483

    Elliott Whitlow (9/22/2011)

    I would create templates for sprocs, views, function and provide them to the DB developers. These templates would include the drop then create as well as the GO statements.

    I would also reject any code that does not fit the spec and make them fix it. It would not take much time before the developers started doing this without prompting because they knew they would end up doing it anyway. And don't take any guff about it. Set policy and then enforce it. The templates help them get it right the first time, the enforcement fixes legacy scripts.

    CEWII

    Same sort of stuff that I do. If it doesn't meet your requirements - reject it and tell them why.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 11 posts - 1 through 10 (of 10 total)

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