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

Views: 558
Reading Time: 3 minutes

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 anyone who works or has worked with Oracle, you know this is a copy of CREATE OR REPLACE, which has been in this DBMS for many, many years and since my first contact with SQL Server (SQL 2005) I have always wondered why Microsoft did not implement this, because it made the daily life 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 ensure 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 objects permission, but it has the same problem as non-generalist code, as you will need to define the object type in the “empty” object creation statement ( 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.