Damir Matešić .blog

MS SQL CREATE OR ALTER

By DM / Aug 13, 2018 / MS SQL
Facebook Twitter Google + LinkedIn Pinterest Email Share...

CREATE OR ALTER is a new MS SQL Server syntax extension introduced from MS SQL Server 2016.

Like the previous described MS SQL DROP IF EXISTS (a.k.a. DIE) after many years MS SQL introduced the possibility to create or alter an object depending if the object already exists or not.

So, let’s remember why this is so nice. Let’s try to create a simple stored procedure for the purpose of this demo (I really don’t remember if I did created it before or drop it).

Example:

CREATE PROCEDURE dbo.sp_SQLNewFunctions
AS
BEGIN
	SELECT 'This demo is cool :)' AS Result
END

"Msg 2714, Level 16, State 3, Procedure sp_SQLNewFunctions, Line 1 [Batch Start Line 0]
There is already an object named 'sp_SQLNewFunctions' in the database."

And as I suspected the stored procedure is already here ☹.

So, now in the old fashioned way and I have many possibilities:

  • I could drop it first and then create it again (if I am sure that the object already exists),
  • I could rewrite the code using ALTER instead of CREATE (if I am sure that the object already exists),
  • To be just sure that the code is bullet proof I could write something like:
IF OBJECT_ID(N'dbo.sp_SQLNewFunctions','P') IS NOT NULL
EXEC('DROP PROCEDURE dbo.sp_SQLNewFunctions');
GO
CREATE PROCEDURE dbo.sp_SQLNewFunctions
AS
BEGIN
	SELECT 'This demo is cool :)' AS Result
END

There are few more possibilities to accomplish the same task but we can agree that all of them are rather complicated and there is also too much of code writing (for lazy programmers) and also an administration nightmare because you must take care to match the object name not once, not twice but three time in the row (three times is three times, but...)!

Finally we get the possibility to CREATE OR ALTER an object. If the object does not exists it will be created. And if exist it will be altered. Simple as that.

Example:

CREATE OR ALTER PROCEDURE dbo.sp_SQLNewFunctions
AS
BEGIN
	SELECT 'This demo is cool :)' AS Result
END

Currently supported objects types by CREATE OR ALTER:

  • STORED PROCEDURES
  • FUNCTIONS
  • TRIGGERS
  • VIEWS

 

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (774.00 bytes)