Freigeben über

Developers Choice: CREATE OR ALTER

  • Artikel

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

  • Anonymous
    November 18, 2016
    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"?
    • Anonymous
      November 18, 2016
      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.
  • Anonymous
    November 18, 2016
    Awesome!
  • Anonymous
    November 20, 2016
    The comment has been removed
  • Anonymous
    November 21, 2016
    The comment has been removed
    • Anonymous
      November 21, 2016
      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!
      • Anonymous
        December 22, 2016
        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.
        • Anonymous
          December 22, 2016
          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.
      • Anonymous
        December 22, 2016
        Also, ALTER, CREATE OR ALTER would be useful for synonyms as well.
  • Anonymous
    January 04, 2017
    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.
    • Anonymous
      January 20, 2017
      Can you please add a Connect item with that request? Thank you!