Share via

Developers Choice: CREATE OR ALTER

  • Article

Cross post with https://aka.ms/sqlserverteam

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') = 1DROP PROCEDURE dbo.mysproc;-- OrIF OBJECTPROPERTY(OBJECT_ID('dbo.usp_mysproc'), N'IsProcedure') = 1
DROP PROCEDURE dbo.mysproc;-- OrDROP 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 OBJECTPROPERTY(OBJECT_ID('dbo.usp_mysproc'), N'IsProcedure') = 1
ALTER 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: 

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 a syntax and usability perspective.

Pedro Lopes (@sqlpto) – Senior Program Manager