SQL Server 2016 Service Pack 1 added the CREATE OR REPLACE functionality, however in SQL Server, it is called CREATE OR ALTER. Finally it is here, this has been asked for since the 90s, it was on the SQL Server wishlist (who remembers that before they had connect :-)
CREATE OR ALTER can be used with the following four object types
STORED PROCEDURES
FUNCTIONS
TRIGGERS
VIEWS
Let's take a look how this all works
If you want to run this code, you need to be on SQL Server 2016 Service pack 1 or higher or on vNext, the @@VERSION function on SP1 and vNext returns the following
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30
Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64) Nov 1 2016 23:24:39
Before they added CREATE OR ALTER, you had a couple of options to create rerunable scripts. The first way was dropping the proc if it existed and having the CREATE PROC in the same script, it looked like this
--old way of dropping a proc then having a create script IF OBJECT_ID('procTest') is not null DROP PROCEDURE procTest GO CREATE PROCEDURE procTest AS BEGIN PRINT (1) END; GO
Another way would be to create a dummy proc if the proc didn't exist already and then just have the ALTER PROC part in your script
--another way is to have a dummy proc created, --that way your alter part is always the same IF OBJECT_ID('dbo.procTest') IS NULL EXEC ('CREATE PROCEDURE dbo.procTest AS RETURN 0;') GO ALTER PROCEDURE procTest AS BEGIN PRINT (1) END; GO
In SQL Server 2016 with Service Pack 1, this becomes so much easier. Here is what it looks like
-- the easier way in sql server 2016 SP1 and up CREATE OR ALTER PROCEDURE procTest AS BEGIN PRINT (1) END; GO
Here is how that looks with a function, pretty much the same as a procedure
-- works the same with functions CREATE OR ALTER FUNCTION fnTest() RETURNS INT AS BEGIN RETURN(1) END; GO
The view looks like this
-- also works with views CREATE OR ALTER VIEW vwTest AS SELECT 1 AS col; GO
With triggers it is pretty much the same, here is the code that you can run, I first created a table otherwise I would not be able to create a trigger
-- first we need a table for the trigger IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = 'BooFar') CREATE TABLE BooFar(id int) GO -- works with triggers CREATE OR ALTER TRIGGER trTest ON BooFar AFTER INSERT, UPDATE AS RAISERROR ('Hello from trigger', 1, 10); GO -- just a test to make sure the trigger works INSERT BooFar values(1) -- you should see this in the message tab /* Hello from trigger Msg 50000, Level 1, State 10 (1 row(s) affected) */
The CREATE OR REPLACE syntax does NOT work with DDL triggers. If you execute this the first time it will run fine.
CREATE OR ALTER TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK;
Now run it again, you will get this error
Msg 2714, Level 16, State 2, Procedure safety, Line 1 [Batch Start Line 77]
There is already an object named 'safety' in the database.
I submitted a connect item for this: https://connect.microsoft.com/SQLServer/feedback/details/3111866
I want them to either disable CREATE OR ALTER for DDL triggers or they should fix it so it doesn't error on a second run
Tor wrap this up, let's drop all these objects we just created, we will do that by using the Drop if exists syntax
-- drop everything by using -- DROP object IF EXISTS DROP TABLE IF EXISTS BooFar DROP PROCEDURE IF EXISTS procTest DROP VIEW IF EXISTS vwTest
You can find the github repo for the series here whatisnewinsqlserver2016, all the code for this post can be found here CREATE Or ALTER
2 comments:
Does TFS (Team Foundation Server) support this too?
Unfortunately SSDT projects does not recognise the new format.. and hence it is not possible to Check in TFS
Post a Comment