Search

CREATE OR REPLACE by BAnVA

Active

391
12
Sign in
 to vote
Type: Suggestion
ID: 127219
Opened: 11/3/2005 4:34:24 AM
Access Restriction: Public
Duplicates: 344991 448946 487763
6
Workaround(s)
Since I couldn't find a category called something like "T-SQL Syntax" I decided to post this here.

Maybe I missed something in the CTP docs, but it appears that I will still have to do object existence checks (i.e. IF EXISTS (SELECT...) DROP....) before running a CREATE or ALTER statement. If I don't have to do this anymore and I just missed it in the docs somewhere please disregard the following and thanks!

You guys did a great job getting 2005 to be more declarative (i.e. CREATE LOGIN). Please make the existence check enhancement to reduce typing and make people's SQL more readable.
Details (expand)
Product Language
English
Version
SQL Server 2005 Community Technology Preview Sep 2005 - Developer Edition (32)
Category
SQL Engine
Operating System
Windows XP SP2 Professional
Operating System Language
English
Proposed Solution
Provide declarative equivalent of existence checks. Something equivalent to Oracle's CREATE OR REPLACE. It could be called something like CREATE OR ALTER.
Benefits
Faster Development
Other Benefits
Faster Development
File Attachments
0 attachments
Sign in to post a comment.
Posted by RhinoMonkey.com on 5/8/2013 at 11:34 AM
+1 Why is voting disabled?
Posted by NeonInk on 3/27/2013 at 6:54 AM
Replace and Alter are two different things in my book. Way different.
Posted by Josh Ashwood on 3/12/2013 at 5:43 PM
Why is voting now disabled on this Active item???????????????????????????
Posted by michael12345 on 1/27/2013 at 1:32 PM
I would love this feature too - strangely I'm getting "voting is no longer enabled on this item" when I try to upvote it. Hopefully this means it's already under development.
Posted by Ted W on 1/4/2013 at 9:33 AM
I would like to see a CREATE or ALTER feature implemented as opposed to the REPLACE; because I do not want an existing object dropped unless I specify DROP. Standard workarounds unfortunately require that a changed object be dropped, including the one generated by SSMS, unless a more involved deployment process is employed.

This feature really needs to be part of the T-SQL standard, especially for non-table objects as these are the ones that get redeployed most often.
Posted by John-Huang on 10/10/2012 at 10:17 AM
Definitely this is the feature SQL Server should have. This can reduce *a lot* complexities while code deployment.
Posted by ErikEckhardt on 7/6/2012 at 11:48 AM
@Mattias Lind:

IF "EXISTS(..) ALTER" doesn't work because if it doesn't exist, I want to CREATE it. In your style, then I'd have to repeate the whole SP body a second time in the ELSE clause. Oops.

Bob Sovers' version comes closest, but it is mighty painful. We're asking for something closer to "CREATE OR ALTER".

My proposal for "ALTERATE" and "RECREATE" avoid collision with Oracle's "CREATE OR REPLACE" and make it very explicitly clear what's going on. Or, CREATE WITH DROP_EXISTING would be okay for the CREATE version, and CREATE WITH ALTER_EXISTING would be fine for the ALTER version.
Posted by Robert Heinig II on 2/29/2012 at 1:59 AM
@Bob Sovers1 -> Good One!!!
Posted by SqlNightOwl on 12/18/2011 at 3:08 PM
I first encountered CREATE OR REPLACE when working on Oracle. To keep it consistent with SQL syntax I'd like CREATE OR ALTER (as posted by ErikEckhardt). That is the effect of CREATE OR REPLACE as the "replace" part does an alter rather than a drop & recreate.

This is a much a cleaner amd simpler approach than every workaround that I've seen.
Posted by Mattias Lind, Sweden on 11/26/2011 at 3:17 AM
I just wonder why whould "CREATE OR REPLACE" be a better option then using "IF EXISTS(...) ALTER". In my point of view, reusing the objects name simply implifies that it's "the same" object, with same inputs/outputs, just another body. "C_O_R"-syntax means a "new" object with all the permissions sat aginst it dropped.

I vote no for this, as I feel the request is a "not-needed" request.

alter
To change is distinctively to make a thing other than it has been, in some respect at least; to exchange to put or take something else in its place; to alter is ordinarily to change partially
Synonyms: change, alter, exchange, shift, transmute, commute, metamorphose, substitute, turn, convert, modify, transfigure, vary, diversify, qualify, transform, veer
Posted by ErikEckhardt on 9/15/2010 at 5:52 PM
I propose ALTERATE for "CREATE OR ALTER" and "RECREATE" for "CREATE OR REPLACE" :)

Having both would be great since, as others have commented, ALTER is a very different thing from DROP then CREATE.
Posted by Bob Sovers1 on 8/10/2010 at 8:37 AM
I have changed all my procedure deploy pattern to:

IF OBJECT_ID(N'xxx', N'P') is null
        EXEC sp_ExecuteSQL N'CREATE PROC xxx as RAISERROR(''xxx is incomplete'',16,127);'
GO

    ALTER PROC xxx...

In that way, I am always just altering the proc. If it did not exist, it creates a stub that is supposed to be replaced in the ALTER... batch, but may not be if I have syntax errors :(. Regardless of the ststa of my syntax (and typing skills) I have put my foot in the door with the original stub.

I can then use the sys.objects create_date and modify_date to determine when I first started to deploy the proc, and when it was last modified.
Posted by DenisP2 on 7/20/2010 at 9:17 AM
hey Gurus, imagine you need to create/modify a scalar function across multiple databases. This scalar function may be already used in table column default constraints. In such case you can not rely on IF EXISTS checks at all, b/c SQL Server will not simply let you drop your function. How would you go about replacing existing one (keep in mind in some databases the function may not exist... so you want it created there).

Thanks // Denis
Posted by jamome2 on 5/17/2010 at 7:34 AM
I vote for this feature and like the "CREATE OR REPLACE" syntax
Posted by MG Support on 5/14/2010 at 2:42 PM
I agree that the CREATE or ALTER syntax addition would be great.
Posted by DavidLean on 1/20/2010 at 1:41 PM
Ed W.
You hit an interesting question. i) Are the People here asking for the same functionality for "CREATE OR REPLACE" as defined by a standard or some other vendor. &/OR ii) Are they looking solution to a scripting problem.

Personally, I don't want a command that will DROP & RECREATE my object. I can already do that with 3 lines of TSQL. But when that happens I lose stuff, like Security, Indexes, Data etc.

I want CREATE OR ALTER functionality. Especially in Stored Proc's. Because the CREATE statements need to be in a separate batch & I don't want to DROP them if they already exist as IT Operations may have manually changed who can execute them. I need to ALTER them if they exist. This requres maintaining 2 copies of the SP definition. (or being very creative)    
Posted by Ed W on 1/4/2010 at 4:36 AM
CREATE OR REPLACE is a fairly straightforward concept. It doens't involve an ALTER in any way, but involves REPLACING the object. For example, if you have a table with 10,000 rows in it and you ALTER it to add a column, your rows are preserved. If you CREATE OR REPLACE it, the table is dropped and recreated (REPLACEd) or simply CREATEd if it doesn't already exist.

Having this for the base object types (tables, views, functions and procedures) would be much better than having it for nothing.

Thanks very much for considering our feedback.
Posted by Brian Tkatch on 7/23/2009 at 12:06 PM
This is a good idea.

Though ALTER is misleading. ALTER means to change something about the meta-data. Here the actual PROCEDURE is being replaced. Hence Oracle's CREATE OR REPLACE.

I'd suggest allowing REPLACE on its own, which would implicitly include CREATE. Or change OR to WITH: CREATE WITH REPLACE .
Posted by AKuz on 5/24/2009 at 6:42 AM
One of those cases when a little change would make a huge difference.
Posted by QingsongYao on 1/15/2009 at 8:07 PM
Hello, Guys
I think create or replace can works on two ways:
1) if object exist
            drop it
     create object
2) if object exist
            alter it
     else
            create it
This two have significant different. For example, if I have a function f1 which depends on function f2, then if I use create or replace for function f2, the case 1 will failed because the drop statement will be failed due to dependence checking. Also, no 2 have several advantage, such as the dependence, and permission is not changed, and object id is not changed as well. I personally think it is desirable. However, case 2 can only work with view, function, procedure, trigger, but I think it make sense too. If your guys have any opinion for this, please post it here.
    
Posted by Microsoft on 12/3/2008 at 2:52 PM
Hi,
We understand your concern regarding the CREATE OR ALTER request. We value all customer feedback and sometimes a feature doesn't get into the product due to scoping/resources/priority. Just to give you an idea, we have over 2000 programmability requests to consider for the next version of SQL Server. For every reelease, we go through various scenarios, evaluate customer requests and come up with the features that can fit the schedule given the resources. Sometimes we make painful cuts and end up deferring a request to future.
Anyway, we are definitely evaluating adding the syntax for the next major version of SQL Server. One of the challenges is to decide which DDLs to support first. We cannot add CREATE OR ALTER for every single DDL since that will require lot of work and testing. We will look at scoping the syntax support and provide value to developers for the common scenarios. Hope this helps.

--
Umachandar, SQL Programmability Team
Posted by Steven Wilmot on 11/26/2008 at 4:08 AM
Sadly, I would agree with the statement that "While your response doesn't surprise me, it is a complete joke."

The SQL Development team had about 26 months to respond to the initial comment/request ... There would seem to be two possibilities.
1 - They didn't KNOW about the request (if not, then WHY NOT ? What's the point of us developers providing feedback requests ?)
2 - They didn't CARE about the request (if so, then why was there no further feedback on the connect-site?)

Unlike the "major" new features like "SQLCLR , FILESTREAM or Spatial Types, surely this is a relatively-small feature to add.
This isn't so much "new functionality", but is more "combining two features into one simpler syntax"

I would agree that some sortof "WITH DROP_EXISTING" would be a nice approach
Posted by BAnVA on 2/7/2008 at 4:57 AM
While your response doesn't surprise me, it is a complete joke. I requested this feature in November of 2005 so you've had ample time to work this into the SQL 2008 release.

The good news is that this issue has become irrelevant since I switched to Ruby on Rails and MySQL in January of 2006. Why? Because of Microsoft's lack of direction and complete lack of concern for critical developer requests - like implementing a REAL O/R mapping solution just to name one.
Posted by Microsoft on 2/6/2008 at 10:54 AM
Hello

Thank you for your continued feedback on this feature. We will not be able to support CREATE OR ALTER functionality in the upcoming SQL Server 2008 release. We are actively tracking this feature request and will consider it for a future release of SQL Server.

Thanks

-- SQL Server Engine Team
Posted by quentink on 1/25/2008 at 1:52 PM
Perhaps a "create procedure [...] with drop_existing" syntax? This would be consistent with "create index." In addition to simply making scripting more convenient, this option would allow a procedure to be replaced without the need to replace all permissions, as happens with a drop/create.
Posted by -sg- on 1/20/2008 at 7:53 PM
Please add this feature as a priority
Posted by neupete on 10/17/2007 at 10:59 AM
Seriously guys? Just put it in there. Don't make us wait for Katmai, only to find out it's not in there either, add it to a Service Pack for crying out loud. It's almost embarrasing that we don't have that one small feature.
Posted by Michael J. O_Neill on 9/6/2007 at 9:28 AM
The lack of this feature shouldn't just be "considered". No wonder folks talk about SQL Server as if it were a joke.
Posted by Microsoft on 11/3/2005 at 1:56 PM
Hello,

The "CREATE or REPLACE" like syntax is not part of SQL2005. We are considering it for a future release.

Thanks for your feedback.

Sameer Verkhedkar
sameerv@microsoft.com
[MSFT]
Sign in to post a workaround.
Posted by Sandeep Moturi on 6/20/2011 at 11:43 PM
-- This is a gud option. No need to struggle with single quotes.
if not exists(select 1 from sys.objects where name ='sample' and type='p')

exec('create proc sample as begin    select 1 end') -- stmts in this sp are dummy, so no need of single quotes

go
alter proc sample
as
begin
    --put your original code here
end
go
Posted by Craig Pessano on 3/26/2010 at 10:19 AM
Here is a workaround that allows a procedure to be replaced without the need to replace all permissions, as happens with a drop/create. The CREATE PROCEDURE portion needs to be dynamic SQL because it cannot be combined with other Transact-SQL statements in a single batch.

IF OBJECT_ID(N'[dbo].[xxx]', 'P') IS NULL
EXEC ('CREATE PROCEDURE [dbo].[xxx] AS SELECT 1')
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.xxx
(
@Parm1 INT,
@Parm2 CHAR(1) = NULL
)
AS
SELECT 2
GO
Posted by SQLMonger66 on 11/12/2009 at 9:13 PM
There is another more elegant workaround that is not too bad, although I agree that a declarative solution such as adding a "WITH Replace_Existing" clause would make more sense...

-- Drop the procedure definition if it currently exists.
IF EXISTS (SELECT * FROM sysobjects
    WHERE ID = OBJECT_ID(N'[dbo].[mySP]')
                 AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[mySP]
GO

CREATE PROCEDURE [dbo].[mySP] AS
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO

ALTER PROCEDURE [dbo].[mySP]
@inStartDate DATETIME,
@inEndDate DATETIME
AS
...
Posted by ChooseADifferentHandle on 3/7/2009 at 2:23 AM
I, too, would like to see Create or Replace.

In the mean time you can do something like the following, which would dynamically create the appropriate create or alter statement.

Only caveat...you need to replace qoute(') with two qoutes('') since we are setting the text into a variable.

DECLARE @CreateOrAlter NCHAR(17)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mySP]') AND type in (N'P', N'PC'))
    SET @CreateOrAlter = 'ALTER PROCEDURE'
ELSE
    SET @CreateOrAlter = 'CREATE PROCEDURE'
DECLARE @SPCode NVARCHAR(MAX)
SET @SPCode = @CreateOrAlter+'
[dbo].[mySP]
        (
            @Argument1                NCHAR(64)
        )
        AS
BEGIN
    SELECT ''Hello, World!''+@Argument1
END
'

EXECUTE sp_executesql @SPcode
Posted by -sg- on 5/7/2008 at 2:37 AM
Second workaround, using trigger as an example (from msdn.com):

IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
Posted by -sg- on 5/7/2008 at 2:23 AM
Warning: This workaround is laborious and cumbersome.

IF OBJECT_ID('dbo.<objectname>') IS NOT NULL
DROP <objecttype> dbo.<objectname>
CREATE ...

© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement