Drop and recreate vs alter stored procedure

Question

Drop and recreate vs alter stored procedure

Viewing 7 posts - 16 through 21 (of 21 total)

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

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 1

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.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

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

Dream Weaver Ten Centuries Points: 1104 More actions · Answer 3

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;

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

david.wright-948385 SSCarpal Tunnel Points: 4039 More actions · Answer 5

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.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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