Drop and recreate vs alter stored procedure

  • I'll have to check into that myself. I never bothered to look and we do need that info.

    Thanks, Lynn & Grant.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • see it now

    only has a modified date though. a DDL trigger will have the actual SQL of the change. we have it in the tickets opened for the changes, but it's nice to have it in one place for SOX auditors. and we also have a special copy of a database for a business partner that we never update with regular production changes. people rarely use it, but when there is an error we have to hunt down the change from a month ago or so

  • homebrew01 (4/27/2010)


    Brandie Tarvin (4/27/2010)


    For the purposes of SDLC, Drop and Create gives you a date to which you can trace back changes that broke the proc, if something gets broken. The system tables don't tell you modification dates, so tracing back changes is difficult at best, impossible at worst (if you don't have an alternative tracking system).

    I think that was correct in earlier versions of SQL Server, but in SQL Server 2008 it is not correct. Try this:

    create proc testAlterDate as select GETDATE();

    go

    SELECT SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME AS ProcName, LAST_ALTERED-- result: 2010-04-28 12:08:02.733

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_TYPE = 'PROCEDURE'

    AND LAST_ALTERED > GETDATE() - 7;

    -- wait a bit...

    alter proc testAlterDate as select 3;

    SELECT SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME AS ProcName, LAST_ALTERED-- result: 2010-04-28 12:08:28.053

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_TYPE = 'PROCEDURE'

    AND LAST_ALTERED > GETDATE() - 7;

  • alen teplitsky (4/28/2010)


    see it now

    only has a modified date though. a DDL trigger will have the actual SQL of the change. we have it in the tickets opened for the changes, but it's nice to have it in one place for SOX auditors. and we also have a special copy of a database for a business partner that we never update with regular production changes. people rarely use it, but when there is an error we have to hunt down the change from a month ago or so

    This illustrates one of the best methods for this. Having controls in place, tracking those requests, and limiting access to that information.

    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

  • Another advantage of "create/alter" over "drop/create" is that the latter leaves the database without the object for a (usually short) amount of time.

  • david.wright-948385 (4/29/2010)


    Another advantage of "create/alter" over "drop/create" is that the latter leaves the database without the object for a (usually short) amount of time.

    True, but since it's usually run within a batch, you'll be covered by blocking, etc. But, when there's an error in the script run during CREATE, you can lose the object entirely.

    That said, I still find just keeping CREATE scripts in source control and then using some third party software to generate the build routine works best. Storing an alter script and a create script just leads to misery.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Grant Fritchey (4/29/2010)


    david.wright-948385 (4/29/2010)


    Another advantage of "create/alter" over "drop/create" is that the latter leaves the database without the object for a (usually short) amount of time.

    True, but since it's usually run within a batch, you'll be covered by blocking, etc. But, when there's an error in the script run during CREATE, you can lose the object entirely.

    That said, I still find just keeping CREATE scripts in source control and then using some third party software to generate the build routine works best. Storing an alter script and a create script just leads to misery.

    That is my preferred method too. It also helps during code review to see what was actually changed;-)

    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 7 posts - 16 through 21 (of 21 total)

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