Friday, November 18, 2016

CREATE OR ALTER In One Statment

When ever we execute stored procedure script, we always drop the existing one and recreate it.

IF EXISTS (SELECT * FROM sys.procedures WHERE Name = 'Test')
BEGIN
DROP PROC Test
END
GO

CREATE PROCEDURE Test
AS
BEGIN
       SELECT 1
END

However, with SQL Server 2016 SP1, now there is easy way of doing with one statement.

CREATE OR ALTER PROCEDURE Test
AS
BEGIN
         SELECT 1
END


Obvious advantage is this is much cleaner now. This feature is available for Functions, Triggers and Views as well. 

There is an another important benefits out of this.

If you check the stored procedure stats for the drop and create method by running the following query, you will end up with below results. 

SELECT name,create_date,modify_date
 FROM sys.procedures WHERE Name = 'Test'


In this, both the create and the modify dates are same. However, for the new method, now it has a different modify date which means you can track your objects much better. 






1 comment:

  1. This was a feature in Oracle from 8i days onward (i.e 1998 onward :D)

    They use it as :

    CREATE OR REPLACE PROCEDURE .......

    ReplyDelete