DROP and CREATE vs ALTER

24

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

24 thoughts on “DROP and CREATE vs ALTER

  1. Bryan says:

    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( …

      • Bryan says:

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

  2. sqlnightowl says:

    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!

  3. Dave Hubbard says:

    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.

  4. rik says:

    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.

  5. Chuck says:

    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.

  6. vps says:

    This blog is great. I realy like it!

  7. Carolyn says:

    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?

  8. Carolyn says:

    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.

  9. Dan says:

    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.

      • Dan says:

        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”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,653 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: