Drop and recreate vs alter stored procedure

Question

Post reply

Drop and recreate vs alter stored procedure

SQL*

SSCertifiable

Points: 6638
More actions
April 26, 2010 at 10:55 pm

#144459

HI,
i have script like Drop and Create stored procedure, instead of this script we can use alter script also, is there any advantages over each other.
ex:
if ( object_id('st_name')>0)
begin
drop proc dbo.st_name
end
go
create proc st_name
as
begin
------code
end
thanks
🙂

Viewing 15 posts - 1 through 15 (of 21 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

If you drop and recreate an object, you have to grant all the permissions again to the users.

If you alter an object, all the existing permissions will be remain.

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 2

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).

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.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

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).

That's not a good situation to be in. Everyone really should have some controls in place.

We've implemented:

-- Comments and revision # when changing code

-- Idera's Compliance Manager

-- I built a job to script all SPs, views, tables out to a folder once a week in case I want to refer back to an approximate point in time

-- Archive SP code as previous version before modifying

vidya_pande SSCertifiable Points: 5275 More actions · Answer 4

It would have been very good idea if SSMS scripting utility provides option to create procedures with alter statements.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

If you're keeping your database code in source control (which, if you're not, you should), you either need to use the drop/create or you have to maintain two sets of code. Or, you can start looking at third party tools (Microsofts DB Pro, Red Gate's SQL Compare, Idera's SQL Comparison, Apex SQL Diff, others) that can generate either a drop/create or an alter as needed. This way versions are maintained inside source control, not through comments in the database (the comments are still needed, but versioning is not where that should be maintained).

"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

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

if you use alter stored procedure, you can have a DDL trigger in your database to write the record of the change to a table. i'm about to start doing this for most of our servers and databases.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

I like what homebrew01 has done. You need to document, but also automate a check on changes. It's not usually someone trying to sneak things in (though they do), it's that people forget to update things, or they make a change to test something and then when it works, they move on, not going back to fill in the documentation.

I prefer drop/creates, along with source control, permissions in the scripts, and an auditing mechanism that looks for changes.

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 8

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).
That's not a good situation to be in. Everyone really should have some controls in place.

We do have controls in place. We use Team Foundation Server for our source control, require developers pass code through the DBA team for verification, require documentation and use Drop/Creates because it's easier for us to locate the release based on the proc's create date.

Also, if we have a problem and find the proc DOESN'T have a recent create date, we can point out that the proc probably wasn't updated.

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.

Richard M. SSCertifiable Points: 7401 More actions · Answer 9

We have a database DDL trigger in place that records any changes made to any object. This allows to rapidly go back to a previous version if something breaks. Developers are required to pass the code trough the DBA team for verification.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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).

Actually, starting with SQL Server 2005 the system tables actually do track the modify date of database objects.

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

Lynn Pettis (4/28/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).
Actually, starting with SQL Server 2005 the system tables actually do track the modify date of database objects.

how would i look at the data?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

alen teplitsky (4/28/2010)
Lynn Pettis (4/28/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).
Actually, starting with SQL Server 2005 the system tables actually do track the modify date of database objects.
how would i look at the data?

Which data?

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

which tables would i look into to track changes to user tables?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 14

Lynn Pettis (4/28/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).
Actually, starting with SQL Server 2005 the system tables actually do track the modify date of database objects.

Take a look at sys.objects.

"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