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.
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:
- STORED PROCEDURES (including natively compiled)
- FUNCTIONS (including natively compiled)
* 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