One of the fundamentals that we learnt when learning SQL, that we use CREATE to create a database object, and ALTER if we needed to change its structure. It was simple and straightforward.
But it was too much of a problem writing a deployment script, where you had to check if the object existed, and then create or alter it based on the result. Or you could drop and re-create the object but that would make you lose all the permissions that have been set on the object. In short it tended to get a little messy. Or of course you could try a clever idea like this one. Putting it in as dynamic SQL was a clever alternative too, but then you had difficult-to-read and difficult-to-maintain scripts.
SQL Server 2016’s Service Pack 1 now gives us the CREATE [OR ALTER] statement, with which you could create a database object or modify in just one go. It’s a feature been asked for, for quite a long time, and it’s finally here.
You can use CREATE OR ALTER on the following types of objects: Stored procedures, Functions, Views and Triggers. It would be nice to have this on tables too, but then we have the whole issue of tables being populated, and the effect that it would have on dependent tables would be all too complicated.
Here is a piece of code comparing usage:
THEN:
IF (SELECT OBJECT_ID(‘TestProcedure’)) IS NULL
EXECUTE(‘CREATE PROCEDURE TestProcedure AS PRINT ”Test”’)
GOALTER PROCEDURE TestProcedure
AS
BEGINPRINT ‘Here”s the real code I wanted to write’
END
NOW:
CREATE OR ALTER PROCEDURE TestProcedure
AS
BEGIN
PRINT ‘Here”s the real code I wanted to write’
END
Data Maul 