Update introduces CREATE OR ALTER Transact-SQL statement in SQL Server 2016

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core

Summary


An update is available that introduces a new Transact-SQL statement, CREATE OR ALTER. This statement performs an internal check for an object's existence. If the object does not exist, a new object will be created. If the object exists, the existing object will be altered to the new definition. The new Transact-SQL statement code example resembles the following:

CREATE OR ALTER VIEW corView AS
SELECT 2 AS [dos];
GO

More Information


This update is included in Service Pack 1 for SQL Server 2016.
Before you apply this update and you alter the definition of programmability objects, you have to perform the following operations:
  • Drop the object (if it exists)
  • Create the object by using the new definition
  • Restore permissions for the object

Or, perform the following operations:
  • If the object does not exist, create the object by using CREATE statement.
  • Otherwise, alter the object by using a separate ALTER statement.

After you apply this update, you can only execute the CREATE OR ALTER statement on the following programmability objects:
  • Views
  • Stored procedures (including natively compiled)
  • User-defined functions (including natively compiled)
  • Triggers (including natively compiled)

The CREATE OR ALTER statement does not apply to the following:
  • Objects that require storage (tables, indexes, indexed views, and so on)
  • Non-programmability objects (such as CREATE ASSEMBLY, CREATE TABLE or CREATE SCHEMA)
  • Deprecated programmability objects (RULE and DEFAULT)
Note CLR user-defined functions are supported starting with SQL Server 2016 SP1 CU1.

References


Learn about the terminology  that Microsoft uses to describe software updates.