SQL SERVER – CREATE OR ALTER Supported in Service Pack 1 of SQL Server 2016

SQL SERVER - CREATE OR ALTER Supported in Service Pack 1 of SQL Server 2016 createalter There are many enhancements which were introduced in Service Pack 1 of SQL Server 2016. If you are a developer, then you can easily understand this productivity improvement. Does below code looks familiar to you? Let us learn about CREATE OR ALTER Supported in Service Pack 1 of SQL Server 2016.

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'sp_SQLAuthority' AND TYPE = 'P')
BEGIN
  DROP PROCEDURE sp_SQLAuthority
END
GO
CREATE PROCEDURE sp_SQLAuthority (@Params ...)
AS
BEGIN
  ...
END
GO

So, if you are deploying any script to SQL Server and you are not aware of the situation in production, whether a stored proc was already previously deployed or not. Developer need to put a check and drop if it’s already there and then create it.

Oracle has been having CREATE or REPLACE from a long time and there have been feedbacks given to the Microsoft SQL product team to do something about it.
CREATE OR ALTER statement
Create or Replace xxx

Finally, they have heard it and introduced this change in Service Pack 1 of SQL Server 2016. Here are the few objects which would allow such syntax

  • Stored procedures
  • Triggers
  • User-defined functions
  • Views

Unfortunately, it would not work for Objects that require storage changes like table, index. It is not allowed for CLR procedure also.

Here is the usage

CREATE OR ALTER PROCEDURE usp_SQLAuthority 
AS
BEGIN
SELECT 'ONE'
END
GO

Do you think this is a good enhancement? I would highly encourage to read below blog on a similar feature. SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause

Were you aware of this? Do you want me to blog about SP1 enhancement?

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Scripts, SQL Server, SQL Server 2016, SQL Service Pack
Previous Post
SQL SERVER – Discovery Report – How to Find Information About Installed Features?
Next Post
SQL Server – PowerShell Script – Getting Properties and Details

Related Posts

8 Comments. Leave new

  • That is fantastic, especially in development when you have procedures in source control and are constantly revising them.

    Reply
  • Muthukumar Balu
    December 23, 2016 3:22 pm

    Hi Pinal,
    Let assume if two developer used same name for the stored procedure means, in this case one person stored procedure will get over written by second one right. How it will be ok?

    Reply
  • Hey Pinal…..Perfect for Dev and other lower environments but I would be uncomfortable leveraging it in Prod environment apart from when it is used to hold temporary data or temporary code….Thank you.

    Reply
    • @Anil – i didn’t get you point.

      Reply
      • Hi Pinal,

        At my last shop when lot many deployment changes were getting canceled/rolled back, DBA team and client SME were asked to streamline the process for deploying the changes in production environment and we decided as follows.

        1.Script Validation: No excuses but get latest copy of production database and restore same on Dev server – deploy the changes, rollback the changes and then re-deploy the changes. All goes fine, script validation is successful.
        2.Code review: Walk through the code and let DBA ask questions/clarifications about the same. DROP, TRUNCATE and ALTER query would certainly be discussed and if a SP is going to be overwritten by new code, it would be renamed and maintained till next release. As pointed out by you, before overriding some piece of code, one should see what is being overridden and without leveraging CREATE OR ALTER, one has to review the existing code.
        3.Deploy only validated script and any new changes without testing should be deployed with application owner’s approval in unusual circumstances.

        While this process may not be full proof but could be helpful to avoid unwanted issues. It is great that Microsoft is listening to its customers and adding value to amazing SQL Server.

        Hope I made myself clear this time !

  • @Anil – You can still use the same process :)

    Reply

Leave a Reply