Developers Choice: CREATE OR ALTER


Today we are starting a new blog post series entitled “Developers Choice”. In the first few posts we will be focusing on SQL Server 2016 SP1 enhancements that focus on the developer community, including all developer DBA’s.

Following in the same reasoning of simplifying DDL statements that was started by the DROP IF EXISTS language change, we have now released CREATE OR ALTER as a new language feature.

This has been a standing request since before the SQL Server 2005 days, with several Connect items over the years requesting this feature. It has been called out as productivity booster for any user that scripts objects in SQL Server, namely in the ISV space, where supporting multiple database engines with minimal language change means shorter development cycles.


Before CREATE OR ALTER, if a developer had to alter the definition of programmability object, then the following operations would be required.

Dropping the object and recreating:

  • Drop the object (if previously existing), depending on the version:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.mysproc') AND OBJECTPROPERTY(id, N"IsProcedure') = 1
DROP PROCEDURE dbo.mysproc;
-- Or
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.mysproc'), N"IsProcedure') = 1
DROP PROCEDURE dbo.mysproc;
-- Or
DROP PROCEDURE IF EXISTS dbo.mysproc;
  • Create the object by using the new definition
CREATE PROCEDURE dbo.mysproc @MyParam int AS (...)
  • Restore permissions for the object
GRANT ALTER ON dbo.mysproc TO [that_user];
GO
GRANT EXECUTE ON dbo.mysproc TO [that_user];
GO
GRANT VIEW DEFINITION ON dbo.mysproc TO [that_user];
GO

Or first check for existence and alter:

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.mysproc')
CREATE PROCEDURE dbo.mysproc @MyParam int AS (...)
-- Or
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.mysproc'), N"IsProcedure') = 0
CREATE PROCEDURE dbo.mysproc @MyParam int AS (...)

Now with CREATE OR ALTER, it’s as simple as:

CREATE OR ALTER PROCEDURE dbo.mysproc @MyParam int AS (...)

CREATE OR ALTER can be used in programmability objects such as:

* CLR UDF support introduced with SQL Server 2016 SP1 CU1.

But cannot be used in:

  • Objects that require storage (tables, indexes and indexed views)
  • CLR user-defined functions
  • Deprecated programmability objects (RULE and DEFAULT)
  • Non-programmability objects (such as CREATE ASSEMBLY, CREATE TABLE or CREATE SCHEMA). On these objects, the syntax for CREATE and ALTER is very different from a syntax and usability perspective.

Note: A user with ALTER permissions on a pre-existing object (or ALTER ANY DATABASE permission) will be able to use the CREATE OR ALTER syntax.

Pedro Lopes (@sqlpto) – Senior Program Manager

Comments (11)

  1. Sébastien Sevrin says:

    Nice feature, I can see a lot of scripts that will be simplified (when support for pre-2016 will be dropped).

    I have a question though:
    Will a user with alter only permissions on an object (say, a SP), be able to alter the object if it exists while doing a "create or alter"?

    Or will he have the standard error message "CREATE PROCEDURE permission denied in database"?

    1. Yes, as long as the user has been granted ALTER permission on the object, or has ALTER ANY DATABASE permission, then that user can use CREATE OR ALTER on pre-existing objects. I have added this note to the post.

  2. Jeffrey Ng says:

    This is a god send! Cannot count the number of times this affects deployments to production when replication is involved. Can never count of non productive environments to up to date so this is a welcomed feature! Thanks guys!

  3. Josh says:

    Does "CREATE TYPE xxx AS TABLE" count as a programmability object or storage object in this context.
    Wondering how CREATE OR ALTER works with Table Valued Parameters to stored procedures.

    1. There is no ALTER TYPE, and as such, no support under CREATE OR ALTER. There is a connect item tracking this request at https://connect.microsoft.com/SQLServer/feedback/details/319134/msft-mso-support-alter-type.
      Please add your vote and requirements there so we can more easily discuss it internally.
      Thank you!

      1. MgSm88 says:

        I concur that ALTER TYPE, and thus, CREATE OR ALTER TYPE is sorely needed. Adding/removing fields on a type is a huge pain right now.

        1. Thank you for the feedback. Can you please add a connect item and be descriptive on the pain that you identify, and a scenario where extending the syntax is a plus, that would be helpful.

      2. MgSm88 says:

        Also, ALTER, CREATE OR ALTER would be useful for synonyms as well.

  4. Chirag Shah says:

    Any chance toolset like Management Studio can support scripting with CREATE OR ALTER? This will indeed help for deployment of programmability objects between various environment.

    1. Can you please add a Connect item with that request? Thank you!

Skip to main content