DROP and CREATE vs ALTER
25February 25, 2013 by Kenneth Fisher
When writing a script to change a piece of code, say a stored procedure or view, there are 3 basic options. I’ll start at the outset by saying I use all 3 and that they each have their pluses and minuses. I’m not trying to advocate one or another, just describe each so you can make an informed decision which to use. An important thing to remember that affects all of these methods is the fact that CREATE and ALTER statements have to be the first statement in a batch.
First DROP and CREATE. This method involves first checking to see if an object already exists and if it does exist dropping it. Next you can create the object freely. The script will be similar to this:
IF OBJECT_ID(‘MySP’)>0 THEN
DROP PROCEDURE MySP
GO -- Required so that the CREATE is the first command of a batch.
CREATE PROCEDURE MySP …..
Upsides: Simple to code and maintain. It doesn’t matter if the object does or does not exist.
Downsides: Any specific permissions to the object are going to be lost. By specific permissions I mean granting permissions to the object, not to the schema or database. This happens because when the object is dropped and re-created it is assigned a new object id. This object id is used for references between the various system tables/views. This won’t affect code that refers to the object because those references are done by name which of course isn’t changing.
Next: ALTER. Alter is the simplest way to change the object. In this method you just “alter” the code.
ALTER PROCEDURE MySP ….
Upsides: This is the simplest of the three methods, although admittedly not by much. Any specific permissions to the object are maintained. See above for why.
Downsides: Only works if the object already exists. This means that this method may not work with automated processes used to push code onto an instance.
Last and certainly not least involves using an IF statement to check if the object already exists and use CREATE if doesn’t or ALTER if it does. Now if you remember from above where I said that CREATE and ALTER statements have to be the first thing in the batch this may seem a bit difficult. You can’t have an IF then a CREATE like this:
IF OBJECT_ID(‘MySP’) > 0
CREATE PROCEDURE MySP ….
So how do you do it? Dynamic SQL of course.
IF OBJECT_ID('MySp') IS NULL
SET @sql = 'CREATE MySp ...'
ELSE
SET @sql = 'ALTER MySp ...'
EXEC sp_executesql @sql
Upsides: This is the most flexible method. The object is created when it needs to be and altered if it already exists. This means that if it already exists any changes won’t affect existing permissions, and yet it can still be run when the code doesn’t exist yet and not give an error.
Downsides: This is by far the most complicated method to implement and to maintain. I’m not going to go into any detail as to why. If you’ve ever done anything more than basic dynamic SQL you already know. If you haven’t you’ll find out.
SQL Studies 
Or, you can preface your ALTER command with an IF statement that creates a stub procedure or function if it doesn’t exist:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = MyProcName AND type = ‘P’)
BEGIN
CREATE PROCEDURE dbo.MyProcName AS SELECT 1 AS Temp;
END
GO
ALTER PROCEDURE MyProcName( …
No dynamic SQL AND no loss of permissions or metadata about when the procedure was created, etc.AND if the new definition fails to compile for whatever reason the old one, if there was one, remains in place!
Not a bad idea but you will need to make a minor change. You can’t put the CREATE PROCEDURE after the IF. You could however use dynamic SQL.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = MyProcName AND type = ‘P’)
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql = ‘CREATE PROCEDURE dbo.MyProcName AS SELECT 1 AS Temp;’
EXEC sp_executesql @sql
END
GO
ALTER PROCEDURE MyProcName( …
Busted!
Yep, that’s pretty much how I actually do it. I got careless genericizing the example.
I also add a print statement that echoes out the sql string plus the DB_NAME() just for sanity checking that it got created in the correct database since I work on several daily. And of course lots of little changes if you’re creating a function instead of a procedure.
Also you might try using OBJECT_ID(objectname) IS NOT NULL instead of exists. I don’t know that it’s any faster but it’s certainly shorter.
Here is a test I did to be sure I remembered it correctly.
SELECT object_id(‘sysobjects’), object_id(‘sys.sysobjects’), object_id(‘no’)
For SQL Server I use the object existance check to CREATE a “stub” object if it doesn’t exist and then have alll scripts use an ALTER statement.
If I’m using Oracle, I use the CREATE OR REPLACE statement. It’s not ANSI, but it’s very handy and is one of the few places where Oracle has it over SQL Server.
I have to say I really like the “stub” idea. One of the best parts of blogging for me is the new things I learn from the comments. And yes CREATE OR REPLACE would be really nice to have!
Another Pro vs Con of these approaches is that,with the DROP/CREATE option there is a small window of time where the script does not exist in the DB between the DROP and the CREATE and thus running programs could fail. Whereas with ALTER, it is transactional, and any running code would wait during the change to the script because the script object is locked during the change. Thus as long as the change does not affect the applicaiton (argument changes for example) it will not have any impact on running programs.
Excellent point, and one I should have remembered. It actually happened to me at one job where the stored procedure I was working on was being called multiple times a second. There was just long enough between the DROP and CREATE for the sp to show up missing a couple of times.
I get the concept of the alter. However, in the examples above, if the procedure does not exist, creating a stub and then later in the script altering doesn’t really address the full issue. I assume that the stub would also have to have scripting to set the correct permissions, otherwise what is gained by the alter later, just symmetry in coding practices.
We have policy that when ‘releases’ are pushed out all procedures are dropped then created and permissions assigned. These pushes are run when the DBA is aware that no users are on the system (maintenance window). If we are pushing out a bug fix or immediate fix, we then use the alter to prevent interrupting users.
Here is a for instance for you. I have written a SSIS package that goes out to each of the servers my team maintains and collects information. Part of how this is managed is using stored procedures. The way I handle updates to the code currently is the DROP and CREATE method. The first thing the process does is “refresh” the code by dropping the SP and re-creating it to make sure that the server has the latest copy. I could certainly use ALTER’s instead, however, by doing it this way when I want to add a new instance to the process all I have to do is add it to my control table and the code is placed on the server automatically.
Which get’s us to the stub idea. Using that method I can do ALTER’s instead of CREATE’s by creating a stub at the beginning then always doing an ALTER. That way on existing instances I’m just doing the ALTER and on new ones I’m creating a stub then doing the ALTER.
We use both at my current employer, but one thing to highlight is that whichever approach you take you should always have your object level grant statements as part of the script, so that shouldn’t be an issue with the create+drop approach. I wouldn’t want to depend on inheriting permissions through alter.
I would think that adding the object level grant’s to the scripts could add a large amount of maintenance work. I can create a script to add a dozen or so permissions in a minute or so. Then having to remember to add those permissions to a series of scripts, or re-scripting the objects would be a lot of extra effort. Also if someone forgets to remove something from one of the scripts after removing a permission you could end up with some security issues.
Now on the other hand if you have some sort of automated scripting happening then you can probably ignore everything I said above.
I’m assuming your scripts are in some sort of source control. Because they should be :>.
Hi Kenneth, can you please guide me how to extract the create procedure or trigger script from DB ??? with this below compatibility
SET @sql = ‘CREATE PROCEDURE dbo.MyProcName AS SELECT 1 AS Temp;’
because my object has single quotes inside the script 😦
I’ve done a couple of posts on writing dynamic SQL (which is what you are doing).
https://sqlstudies.com/2013/07/01/writing-dynamic-sql-a-how-to/
https://sqlstudies.com/2013/06/12/best-practice-recommendations-for-writing-dynamic-sql/
If those don’t help then feel free to comment again or email me and I’ll see if I can’t help you more.
This blog is great. I realy like it!
I run this to check to see if this sp exists in sqlserver 2012. It does not.
But when I run the script to create the SP it always changes CREATE to ALTER and that fails because the sp doesn’t exist?
select
SPECIFIC_NAME AS procedureName,
SPECIFIC_CATALOG AS databaseName,
CREATED AS createDate,
LAST_ALTERED as lastAlteredDate
from
information_schema.routines
where routine_name = ‘usp_CreateMxdFieldQueryExample’
===
This is the SP
USE [rmmscontrols]
GO
/****** Object: StoredProcedure [dbo].[usp_CreateMxdFieldQueryExample] Script Date: 8/14/2014 1:49:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— Create the procedure
CREATE PROCEDURE [dbo].[usp_CreateMxdFieldQueryExample]
(@realTableName nvarchar(50))
AS
— Create a temp table in which the records will be used to update the MxdFieldSetting table
CREATE TABLE #tempTable
(
LayerActualName varchar(50),
FieldActualName varchar(50),
QueryExample varchar(100)
)
— Create a cursor to read each record in the MxdFieldSetting table
DECLARE readMxdFieldSetting CURSOR SCROLL
FOR SELECT LayerActualName, FieldActualName FROM MxdFieldSetting WHERE LayerActualName = @realTableName
— Declare variables
DECLARE @LayerActualName varchar(50)
DECLARE @FieldActualName varchar(50)
DECLARE @FieldType nvarchar(20)
DECLARE @Value nvarchar(100)
DECLARE @QueryExample nvarchar(100)
DECLARE @SqlString nvarchar(1000)
DECLARE @ParaDef nvarchar(1000)
DECLARE @ExistIndex int
— Initialize the variables
SET @LayerActualName = ”
SET @FieldActualName = ”
SET @FieldType = ”
SET @Value = ”
SET @QueryExample = ”
— Open the cursor and start add data to the #tempTable
OPEN readMxdFieldSetting
— Read the first row of data into the variables
FETCH NEXT FROM readMxdFieldSetting INTO @LayerActualName, @FieldActualName
WHILE( @@FETCH_STATUS = 0 )
BEGIN
— RESET the varaibles; added by Myung-Hwa Hwang on 01/03/2012
SET @FieldType = ”
SET @Value = ”
SET @QueryExample = ”
— Get the type of the field
SET @SqlString = N’SELECT @FieldType = DATA_TYPE FROM rmms.information_schema.columns WHERE table_name = ”’ +
substring( @LayerActualName,10,50) + ”’ AND column_name = ”’ + @FieldActualName + ””;
SET @ParaDef = N’@FieldType NVARCHAR(20) OUTPUT’
EXECUTE sp_executesql @SqlString, @ParaDef, @FieldType = @FieldType OUTPUT;
SET @FieldType = LOWER(@FieldType);
— If the type of the field is text, then don’t add a record to the #tempTable
SELECT @ExistIndex = charindex(‘text’,@FieldType);
IF @ExistIndex = 0
BEGIN
— Get a value of the field which should be the value of least frequency and not null in the sde table
SET @SqlString = N’SELECT @Value = ‘ + @FieldActualName + ‘ FROM ‘ + @LayerActualName + ‘ WHERE ‘ + @FieldActualName + ‘ is not null GROUP BY ‘ + @FieldActualName + ‘ ORDER BY COUNT(*) ASC’;
SET @ParaDef = N’@Value NVARCHAR(100) OUT’;
EXECUTE sp_executesql @SqlString, @ParaDef, @Value=@Value OUTPUT;
— Form an example based on the type of the field
SELECT @ExistIndex = charindex(‘int’,@FieldType);
— The type of the field has ‘int’ in it.
IF @ExistIndex 0
BEGIN
SET @QueryExample = N” + @FieldActualName + ‘=’ + @Value;
END
ELSE
BEGIN
— The type of the field has ‘numeric’ in it.
SELECT @ExistIndex = charindex(‘numeric’,@FieldType);
IF @ExistIndex 0
BEGIN
SET @QueryExample = N” + @FieldActualName + ‘=’ + @Value;
END
ELSE — The rest case of the field type is considered as char type.
SET @QueryExample = N” + @FieldActualName + ‘=”’ + @Value + ””;
END
— Add the query example into the temp table
INSERT INTO #tempTable (LayerActualName, FieldActualName, QueryExample) VALUES (@LayerActualName, @FieldActualName, @QueryExample)
END
— Read the next row of data into the variables
FETCH NEXT FROM readMxdFieldSetting INTO @LayerActualName, @FieldActualName
END
— Close the cursor and delete it
CLOSE readMxdFieldSetting
DEALLOCATE readMxdFieldSetting
— Update rows in MxdFieldSetting based on the rows in the temp table
UPDATE MxdFieldSetting
SET QueryExample =
(SELECT QUERYEXAMPLE
FROM #tempTable
WHERE
LayerActualName = MxdFieldSetting.LayerActualName AND
FieldActualName = MxdFieldSetting.FieldActualName)
WHERE LayerActualName = @realTableName
print @realTableName
What is the fix?
I don’t really understand. I see that you are checking for the SP in the initial query then trying to create it. What is changing the CREATE to an ALTER? Is this part of an automated process?
Obviously I over my head in this.
The programmer we had on a different server created this sp and it is used in his code. That was sqlserver 2008. He was a genius. Now we have moved the code to a new server and it uses sqlserver 2012. The code part that references this sp fails. So I was trying to create the sp a new on the new server.
After I delete the sp, before I execute the script again I run the check to see if it exists.
When I copy and paste the sp script into a query window and execute it, The Create is changed to ALTER when I open the sp. And the name of the sp following the ALTER command is underlined in red.
Thank for your help.
It’s no problem. And you aren’t really having one either 🙂 Once you run the create script the sp is created. You are then opening the sp in an “edit” mode (I’m being very lose with terminology here). The reason for the ALTER is that SQL is setting it up so you can make changes to the now already existing sp. You might double check that there isn’t another version of this SP under a different schema name (the dbo in front is the schema) but I doubt it.
I’m commenting here (even though the post is quite old) to share what we’ve been doing with this.
We encountered a situation where we needed to update a remote site, but we didn’t have DROP permissions. For ever, we have been using the ‘DROP and CREATE’ script built into SSMS 2008 R2, but now we needed to change.
We created three templates, which we drop above the appropriate ALTER scripts when we need to update a stored procedure or function:
— Stored Procedure
IF OBJECT_ID(‘[dbo].[]’) IS NULL
EXEC(‘CREATE PROCEDURE [dbo].[] AS SET NOCOUNT ON;’)
EXEC(‘GRANT EXECUTE ON [] TO Public AS dbo;’)
GO
— Scalar Function
IF OBJECT_ID(‘[dbo].[]’) IS NULL
EXEC(‘CREATE FUNCTION [dbo].[] (@i INT) RETURNS INT AS BEGIN RETURN 0 END;’)
EXEC(‘GRANT EXECUTE ON [] TO Public AS dbo;’)
GO
— Table-based Function
IF OBJECT_ID(‘[dbo].[]’) IS NULL
EXEC(‘CREATE FUNCTION [dbo].[] (@i INT) RETURNS @O TABLE(i INT) AS BEGIN INSERT INTO @O SELECT 0 RETURN END;’)
GO
We need that Public receives EXECUTE permissions on these, so we script that into each CREATE. After that, the ALTER doesn’t change it, and if they add or modify the permissions, they remain. Doing it this way, it’s an easy task to copy the name of the function or stored procedure, and use the Template Parameter replacement to automating the completion of these scriptlets.
Now, I’m hoping that the good folks at Microsoft will either add this to their “Script ___ as” lists, or give us the ability to create our own such that this scripting comes ‘baked-in’
I’m not a big fan of granting anything to public but beyond that you might look up a bit in the comments. The IF NOT EXISTS .. CREATE stub .. ALTER works really well.
Other than the minor differences (*) I think the two solutions are pretty much the same (my post didn’t preserve the template replacement <Name_Of_Sproc, , > which I find handy.)
Additionally, as we need to do this for functions, I hadn’t found in one place how to create a minimalist header for scalar functions and table functions. Of course, you would only insert the header you need, not all three of them.
However, if there are issues with how we’re now doing it, I’d love to know!
* “IF NOT EXISTS (SELECT * FROM sys.objects” vs. “IF OBJECT_ID(N'”
* “EXEC sp_executesql” vs. “EXEC”
No, you are right. I wan’t looking closely enough 🙂 It is basically the same thing.
[…] less likely to error. For me it’s right up there with the CREATE OR ALTER pattern. No more DROP then CREATE or ALTER, no more IF EXISTS () DROP […]