THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search
Home Blogs Forums Roller Downloads   Opml View as RSS news feed in XML

Denis Gobo

Should SQL Server Have The CREATE [OR REPLACE] PROCEDURE Syntax?

I was asked by a developer at work the other day why SQL Server does not have the create or replace syntax. I started thinking and there were some advantages and one big disadvantage
First the advantages
 
Advantage
When scripting out a database you don’t have to generate if exists.....drop statements

When modifying an object from a file in source control you don’t have to change back and forth between CREATE and ALTER. This is really annoying sometimes; usually you create a proc or script out a proc and store it in Subversion/SourceSafe. Now you take that file, make some changes to the proc and run it. Of course it will tell you that the proc already exists, in general you don’t want to drop the proc and then execute the CREATE proc statement because then you have to worry about the permissions.(not everyone is running as dbo you know)

Disadvantage
I can overwrite a proc without even knowing it. What if we both are working on a proc and somehow we pic the same name I create my proc, you work in a remote location, you create yours and mine is gone.

Of course it is all what you are used to, I am sure the Oracle guys are not overwriting each other’s procs every day either

So what do you think? Would you be in favor of this syntax or not? Can you think of more advantages or disadvantages?

Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Published Wednesday, May 07, 2008 12:53 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

andyleonard said:

Hi Denis,

  I'm used to the If Exists syntax, so that drives my coding style and determines the things I worry about. One advantage of dropping and recreating stored procedures in SQL Server is this allows me to see the last datetime the procedure was modified at a glance - using the CreateDate.

:{> Andy

May 7, 2008 12:04 PM
 

Alexander Kuznetsov said:

Hi Denis,

First of all, I think that all stored procedures must be in source control, which takes care of the worry about overwriting each other's code. Also I think there are very good tools for deployment, so I don't have to create deploy/rollback scripts manually.

Also let me share a small trick in pseudocode:

IF procedure does not exist BEGIN

 Issue dynamic SQL command to create a stub procedure with this name

END

ALTER PROCEDURE

<all your code goes here>

May 7, 2008 12:14 PM
 

Denis Gobo said:

Andy,

In SQl server 2005 you have both the create and modification date available. run this

create proc Testcreate

as

select getdate()

go

waitfor delay '00:00:10'

go

alter proc Testcreate

as

select getutcdate()

go

select create_date, modify_date,* from sys.objects where name = 'Testcreate'

May 7, 2008 12:15 PM
 

Denis Gobo said:

Alexander,

>>Also I think there are very good tools for deployment, so I don't have to create deploy/rollback scripts manually.

FYI

At some companies, you have to supply scripts and someone else will hit the magic F5 key. If you messed up, they will still come back to haunt you  :-)

May 7, 2008 12:17 PM
 

Alexander Kuznetsov said:

Yep - that's how it works in our company. I use a tool to create deploy/rollback scripts, it compares a copy of production against a database created from scripts checked in source control. I submit these scripts, and the DBAs use tools to test my create and rollback.

May 7, 2008 2:03 PM
 

Vern Rabe said:

I would like to see it. Our current standard is to (conditionally) drop and create, but that standard was decided upon pre SQL Server 2005 so that we could determine when a proc was last modified (via sysobjects.crdate). We also use schemas for execute permissions instead of explicit procedure permissions, so dropping and recreating doesn't cause any permission issues. That being said, the option of using the CREATE [OR REPLACE] would be handy when schema level permissions aren't approprate, plus it allows having meaningful create_date AND modify_date.

May 7, 2008 2:11 PM
 

Hugo Kornelis said:

Hi Denis,

Regarding the disadvantage of accidentally overwriting each others code - if you habitually put an IF EXISTS ... DROP at the start of each script, the exact same happens...

May 7, 2008 2:41 PM
 

David L. Penton said:

I agree with Hugo and Vern.  The issue of two developers doing this is a non-issue as this can happen today.  And, I like the idea of the metadata being meaningful.  Of course, as soon as someone DROPs/CREATEs an object, you lose that information.

May 7, 2008 3:45 PM
 

Chris J. Breisch said:

Your "disadvantage" point is disingenuous.  I run into the exact same problem if I use IF EXISTS ... DROP.

Oh, Hugo beat me to it.  He's correct.

So, you've pointed out an advantage and a disadvantage that also applies to the current scenario, so isn't really a disadvantage.

Sounds like to me that you've answered your own question.  There are only advantages to having it.

May 8, 2008 8:19 AM
 

Dew Drop - May 8, 2008 | Alvin Ashcraft's Morning Dew said:

PingBack from http://www.alvinashcraft.com/2008/05/08/dew-drop-may-8-2008/

May 8, 2008 9:21 AM
 

Denis Gobo said:

>>Sounds like to me that you've answered your own question.  There are only advantages to having it.

I just love it when I answer my own question.

So, only advantages? Why is it not in SQL server yet, Is there a connect link requesting this? It will get my vote

May 8, 2008 11:02 AM
 

Saggi Neumann said:

Hey Denis,

A better post on connect would an option be to enable versioning of SQL code (as an option) like we had on DTS packages in 2000.

What do you think?

May 8, 2008 1:20 PM
 

AaronBertrand said:

Denis: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127219

Saggi:

You can kind of do this with numbered procedures, but this feature is going away, rather than being extended to other objects.

May 8, 2008 2:34 PM
 

Denis Gobo said:

Aaron,

Thanks, I casted my vote (FWIW)

May 8, 2008 2:55 PM
 

tduffy77 said:

It can only help. In our change control system, we don't allow drops in production. The developers want to reuse scripts across all environments (as they should), and alters won't work for new objects on databases freshly restored from production. Create replace (alter) would fit the bill nicely.

May 8, 2008 4:42 PM
 

Saggi Neumann said:

Aaron: I actually thought about it a little after posting the comment, and it can probably be achieved quite gracefully with DDL triggers, saving versions of stored procs, functions, views and table definitions...

May 9, 2008 11:40 AM
 

Log Buffer #96: a Carnival of the Vanities for DBAs said:

PingBack from http://www.pythian.com/blogs/996/log-buffer-96-a-carnival-of-the-vanities-for-dbas

May 9, 2008 12:37 PM
 

Alexander Kuznetsov said:

Saggi,

Why would you want to do that? Any source control system (my favorite one is Subversion) will do it for you, and much much more, and all your source code should be checked into a source control system anyway.

May 12, 2008 9:07 PM
 

Saggi Neumann said:

Alexander,

As a SQL Server consultant, I can tell you that many of my customers don't have their SQL code in source control. Some of them don't even have their application code in source control (yeah... i know...).

So - you're right - all source code should be managed in a source control environment. But since source control unfortunatly isn't used everywhere, deploying DDL triggers to at least keep things under control in the SQL domain is a pretty straightforward and fast solution.

May 13, 2008 4:01 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication
©2006-2008 SQLblog.comTM
Brought to you by Peter DeBetta & Adam Machanic
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement