SQL Server 2016 SP1 - CREATE OR ALTER in procedures, functions, triggers and views

Hello people,
Good afternoon!

In this post I will demonstrate a new Transact-SQL feature available from SQL Server 2016 SP1, which is the CREATE OR ALTER command, which can be applied to procedures, functions, triggers and views.

For those who work or have worked with Oracle, you know that this is a copy of CREATE OR REPLACE, existing in this DBMS for many, many years and that since my first contact with SQL Server (SQL 2005) I always wondered why Microsoft did not implement this, as it made the day-to-day of DBA's and developers much easier.

Until the creation of this feature, for procedures, functions, triggers and views it was possible to use 3 commands:
- CREATE
- ALTER
- DROP

If the object in question did not exist in the base and you executed the ALTER command, SQL returned an error message:

If the object in question existed at the base and you ran the CREATE command, SQL also returned an error message:

So how did DBA / Developer do to prevent these error messages from occurring while updating objects?

If it exists, it erases and then creates

One way to ensure that the execution does not return an error is to check if the object exists in the base and if it exists, delete the view / procedure / function / trigger. In this solution, I see two major problems:

• DROP code needs to be explicitly declared with the object type (DROP VIEW, DROP FUNCTION, DROP PROCEDURE, or DROP TRIGGER), so our code is not as generic.
• Deleting the object and recreating it shortly afterwards, the permissions on that object are lost and users who had access to that object will no longer have it. To prevent this from happening, you will have to save the permissions of each object before deleting and re-creating it, which can be very labor intensive depending on the number of objects to be changed in the base, as well as the risk and responsibility to return all permissions for all objects. To save the permissions, you can use the script I provided in the post. Checking a user's permissions in SQL Server.

Example of use:

If not, create and then change

Another way to guarantee the creation / alteration of objects without error, is with the solution that I will present below, where I check if the object exists and if it does not exist, create an “empty” object and after that, execute the ALTER command with the correct source code.

This solution is better than the previous one, as it does not have the problem of losing the permission of the objects, but it has the same problem as the code is not generalist, since you will need to define the type of the object in the instruction to create the “empty” object CREATE VIEW, CREATE PROCEDURE, etc ..)

Example of use:

CREATE OR ALTER

Feature made available from SQL Server 2016 SP1, you can now use the CREATE OR ALTER statement when creating Stored Procedures, Functions, Views, and Triggers. In doing so, SQL Server itself will check to see if the object exists and will create it if it does not exist with the source code entered or make a change to the code if the object already exists.

Example of use:

Result:
Command (s) completed successfully.

That's it, guys.
I hope you enjoyed this post and see you next time.