What do you do in SQL Server to CREATE OR ALTER?

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead. For triggers, you have to lean on the proprietary system views. Is this the most accepted convention in the meantime?

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE') EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog') CREATE PROCEDURE dbo.SynchronizeRemoteCatalog AS BEGIN -- body END 

For triggers, you have to lean on the proprietary system views.

Is this the most accepted convention in the meantime?

EDIT: As n8wrl suggested, the official word suggests that this feature is not a high priority. Hence the question.

3

14 Answers

This article makes a good point about losing permissions when dropping an object in SQL server.

So here is the approach which retains permissions:

IF OBJECT_ID('spCallSomething') IS NULL EXEC('CREATE PROCEDURE spCallSomething AS SET NOCOUNT ON;') GO ALTER PROCEDURE spCallSomething ... --instead of DROP/CREATE 

Also works for functions, just replace PROCEDURE with FUNCTION in the above code.

Another reason to consider doing it this way is tolerance to failure. Suppose your DROP succeeds, but your CREATE fails - you end with a broken DB. Using ALTER approach, you will end up with an older version of the object.

5

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE.

The year is 2016 and it does now have DIE (Drop If Exists) in SQL Server 2016 RTM and CREATE OR ALTER (introduced in 2016 SP1).

Taking Drop If Exists first the caveats around needing to re-apply permissions with this approach still apply. Example syntax is

DROP PROCEDURE IF EXISTS dbo.SynchronizeRemoteCatalog GO CREATE PROCEDURE dbo.SynchronizeRemoteCatalog AS BEGIN BODY: END GO /*TODO: Reapply permissions*/ 

CREATE OR ALTER retains the permissions. Example syntax is

 CREATE OR ALTER PROCEDURE dbo.SynchronizeRemoteCatalog AS BEGIN BODY: END 

The corresponding MSSQL Tiger Team blog post explains

CREATE OR ALTER can be used in programmability objects such as:

  • STORED PROCEDURES (including natively compiled)
  • FUNCTIONS (Transact-SQL, including natively compiled)
  • TRIGGERS
  • VIEWS

But cannot be used in:

  • Objects that require storage (tables, indexes and indexed views)
  • CLR user-defined functions
  • Deprecated programmability objects (RULE and DEFAULT)
  • Non-programmability objects (such as CREATE ASSEMBLY, CREATE TABLE or CREATE - SCHEMA). On these objects, the syntax for CREATE and ALTER is very different from a syntax and usability perspective.

We encountered a situation where we needed to update a remote site, but we didn’t have DROP permissions. Until now, we have been using the ‘DROP and CREATE’ script built into SSMS 2008 R2, but now we needed to change. We created three templates, which we drop above the appropriate ALTER scripts when we need to update a stored procedure or function:

—- Stored Procedure IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL EXEC('CREATE PROCEDURE [dbo].[<Name_Of_Routine, , >] AS SET NOCOUNT ON;') EXEC('GRANT EXECUTE ON [<Name_Of_Routine, , >] TO Public AS dbo;') GO —- Scalar Function IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL EXEC('CREATE FUNCTION [dbo].[<Name_Of_Routine, , >] (@i INT) RETURNS INT AS BEGIN RETURN 0 END;') EXEC('GRANT EXECUTE ON [<Name_Of_Routine, , >] TO Public AS dbo;') GO —- Table-based Function IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL EXEC('CREATE FUNCTION [dbo].[<Name_Of_Routine, , >] (@i INT) RETURNS @O TABLE(i INT) AS BEGIN INSERT INTO @O SELECT 0 RETURN END;') GO 

Any special permissions get scripted after each CREATE (Table functions cannot be assigned permissions). After that, the ALTER doesn’t change it, and if they add or modify the permissions, they remain. Doing it this way, it’s an easy task to copy the name of the function or stored procedure, and use the Template Parameter replacement to automating the completion of these scriptlets.

Now, I’m hoping that the good folks at Microsoft will either add this to their “Script ___ as” lists, or give us the ability to create our own such that this scripting comes ‘baked-in’

You may want to throw some weight behind the SQL Server feedback entry at: https://connect.microsoft.com/SQLServer/feedback/details/344991/create-or-alter-statement. It seems to be one of the few that are still accessible publicly, and they state that they "have started a feasibility review for this to decide if we can ship this in the near future." The more voices, the more likely this will happen!

(Update: now also using the following code for Triggers and Views)

-- Triggers IF OBJECT_ID('[dbo].[<Name_Of_Trigger, , >]') IS NULL -- Check if Trigger Exists EXEC('CREATE TRIGGER [dbo].[<Name_Of_Trigger, , >] ON [<Name_Of_Table, , >] AFTER UPDATE AS SET NOCOUNT ON;') -- Create dummy/empty SP GO -- Views IF OBJECT_ID('[dbo].[<Name_Of_View, , >]') IS NULL -- Check if View Exists EXEC('CREATE VIEW [dbo].[<Name_Of_View, , >] AS SELECT 1;') -- Create dummy/empty View GO 
0

Every time a developer writes IF EXISTS(...) DROP a seal pup is clubbed. You should know exactly what's in the database and your upgrade script should do the CREATE or ALTER as appropriate, based on the current version of your application schema: Version Control and your Database.

8

I'd use OBJECT_ID(...) IS NOT NULL before a DROP.

Object identifiers have to be unique, so it works without using system tables:

CREATE TRIGGER dbo.ExistingTable ON dbo.AnotherTable FOR UPDATE AS SET NOCOUNT ON GO 

gives

Msg 2714, Level 16, State 2, Procedure MetaClass, Line 3 There is already an object named ExistingTable ' in the database. 

I normally use ALTER because of how we work with source control, etc.

3

I always alter my objects because a drop is really bad practice and can leave your DB in an bad state if an object fails to create (24/7 db!), as well as what the other posters have mentioned about nuking permissions.

Editors like Sublime, Atom, and VS Code will let you make code snippets as templates for these to quickly gen-up your skeleton script. SQL 2016 now finally supports DROP IF EXISTS construct, but it still approaches from the wrong direction - that everything is a drop/create instead of a one time create in the distant past and alter from then on. Also, I have tried to make my headers as short as will possibly work, so I don't get any fancier than create proc dbo.myproc as as the create stub.

Views:

if objectproperty(object_id('dbo.myview'), 'IsView') is null begin exec('create view dbo.myview as select 1 c') end go alter view dbo.myview as -- select * -- from table go 

Procs:

if objectproperty(object_id('dbo.myproc'), 'IsProcedure') is null begin exec('create proc dbo.myproc as') end go alter procedure dbo.myproc as set nocount on -- Add the stored proc contents here... go 

UDF (scalar):

if objectproperty(object_id('dbo.myudf'), 'IsScalarFunction') is null begin exec('create function dbo.myudf returns int as begin return null end') end go alter function dbo.myudf(@s varchar(100)) returns int as begin -- return len(@s) end go 

UDF (tabular):

if objectproperty(object_id('dbo.myudf'), 'IsTableFunction') is null begin exec('create function dbo.myudf returns @t table(x int) as begin return end') end go alter function dbo.myudf(@s varchar(100)) returns @result table ( -- Columns returned by the function id int identity(1, 1) primary key not null ,result varchar(100) null ) begin return end go 
1

That's basically the way to do it, yes. I just wonder if you have a particular reason to use the "EXEC" approach:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE') EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog') 

Why not just:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE') DROP PROCEDURE dbo.SynchronizeRemoteCatalog 

???

For triggers, there's sys.triggers. Those are system catalog views in the "sys" schema - not strictly or directly tables, really.

Marc

1

The year is 2017 and SQL Server has CREATE OR ALTER

SQL Server 2016 SP1 and SQL Server vNext have new T-SQL language statement – CREATE [OR ALTER] for:

  • STOREDPROCEDURES
  • FUNCTIONS
  • TRIGGERS
  • VIEWS

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/

1

I prefer CREATE-ALTER approach (not syntax) over DROP-CREATE for two reasons:

  • permissions (with DROP-CREATE you have to recreate them)
  • object_id (altering object won't change it)

Example DROP-CREATE:

--Initial creation: CREATE PROCEDURE dbo.my_proc AS SELECT * FROM dbo.a WHERE i < 10; GO SELECT OBJECT_ID('dbo.my_proc'); GO -- Recreating DROP PROCEDURE IF EXISTS dbo.my_proc; GO CREATE PROCEDURE dbo.my_proc AS -- some meaningless comment SELECT * FROM dbo.a WHERE i < 10; GO SELECT OBJECT_ID('dbo.my_proc'); GO 

DB Fiddle

As we can see the object_id has changed.

Example 2: CREATE-ALTER

-- Initial creation CREATE PROCEDURE dbo.my_proc2 AS SELECT * FROM dbo.a WHERE i < 10; GO SELECT OBJECT_ID('dbo.my_proc2'); GO -- Altering CREATE OR ALTER PROCEDURE dbo.my_proc2 AS -- some meaningless comment SELECT * FROM dbo.a WHERE i < 10; GO SELECT OBJECT_ID('dbo.my_proc2'); GO 

DB Fiddle

In this scenario the object_id remains the same.

Sample scenario when this can cause some problems. Let's assume that we use SQL Server 2016 Query Store and force to use specific query plan for stored procedure.

DROP-CREATE

USE T1; GO -- make sure that Query Store is READ_WRITE IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[a]( [i] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [g] [uniqueidentifier] NULL, [z] VARCHAR(10) ); END GO -- populate table (15k records) INSERT INTO dbo.a(g, z) SELECT NEWID(), number FROM (SELECT CAST([key] AS INT) AS number FROM OPENJSON( '[1' + REPLICATE(',1',3000-1)+']') ) AS num GO 5 -- initial creation CREATE PROCEDURE dbo.my_proc AS SELECT * FROM dbo.a WHERE z LIKE '12%' AND 1 = (SELECT 1); GO -- Clustered Index Scan EXEC dbo.my_proc; EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; GO --dc1 -- creating index CREATE NONCLUSTERED INDEX IX_dbo_a_z ON dbo.a([z] ASC) INCLUDE ([i], [g]); GO -- index seek EXEC dbo.my_proc; EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; -- forcing plan GUI, clustered scan -- dc3 EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; -- dc4 -- Clustered Index Scan EXEC dbo.my_proc; EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; -- dc5 /* MAIN PART - DROP - RECREATE */ DROP PROCEDURE IF EXISTS dbo.my_proc; GO CREATE PROCEDURE dbo.my_proc AS -- some meaningless comment added by developer SELECT * FROM dbo.a WHERE z LIKE '12%' AND 1 = (SELECT 1); GO /* MAIN PART END */ -- Index Seek EXEC dbo.my_proc; EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; -- object_id in query store is NULL -- is_forced_plan flag is ignored !!! 

First execution:
DC1

Adding index and execute: enter image description here

Forcing plan: enter image description here enter image description here

Another execution: enter image description here

After DROP-CREATE: enter image description here

CREATE - ALTER

USE T2; GO -- make sure that Query Store is READ_WRITE IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[a]( [i] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [g] [uniqueidentifier] NULL, [z] VARCHAR(10) ); END GO -- populate table (15k records) INSERT INTO dbo.a(g, z) SELECT NEWID(), number FROM (SELECT CAST([key] AS INT) AS number FROM OPENJSON( '[1' + REPLICATE(',1',3000-1)+']') ) AS num GO 5 -- initial creation CREATE PROCEDURE dbo.my_proc AS SELECT * FROM dbo.a WHERE z LIKE '12%' AND 1 = (SELECT 1); GO -- Clustered Index Scan EXEC dbo.my_proc; EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; -- ca1 GO -- creating index CREATE NONCLUSTERED INDEX IX_dbo_a_z ON dbo.a([z] ASC) INCLUDE ([i], [g]); GO -- index seek EXEC dbo.my_proc; EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; --ca2 -- forcing plan GUI --ca3 EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; --ca4 -- Clustered Index Scan EXEC dbo.my_proc; EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; --ca5 GO /* MAIN PART - CREATE-ALTER */ CREATE OR ALTER PROCEDURE dbo.my_proc AS -- some meaningless comment added by developer SELECT * FROM dbo.a WHERE z LIKE '12%' AND 1 = (SELECT 1); GO /* MAIN PART END */ -- Clustered Index Scan EXEC dbo.my_proc; EXEC sp_query_store_flush_db; SELECT qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.[object_id], OBJECT_NAME(qsq.[object_id]) AS [object_name], qsp.is_forced_plan, qsqt.query_sql_text, qsrs.count_executions, CAST(qsp.query_plan AS XbML) AS sql_query_plan FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id= qsp.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id WHERE query_sql_text LIKE '%dbo.a%' AND qsq.[object_id] <> 0 ORDER BY qsq.query_id; -- is_forced_plan is valid 

First execution:
enter image description here

Adding index and execute: enter image description here

Forcing plan: enter image description here enter image description here

Another execution: enter image description here

After CREATE-ALTER: enter image description here

Result

With Drop-Create we lost forced plan.

Looks like it's a while off: link text

typical script for me:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ig_InsertDealer' AND type = 'P') DROP PROC dbo.ig_InsertDealer GO CREATE PROCEDURE dbo.ig_InsertDealer ... GO GRANT EXECUTE ON dbo.ig_InsertDealer TO ... GO 
1

I'll use either depending on context: my initial-build or major refactoring scripts will use check/drop/create, pure maintenance scripts use alter.

I have a template, which allows to execute a script several times without errors.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[aaa_test]') AND type in (N'P', N'PC')) EXEC('CREATE PROCEDURE aaa_test AS') EXEC('GRANT EXECUTE ON aaa_test TO someone') GO ALTER PROCEDURE aaa_test @PAR1 INT, @PAR2 INT=0 AS BEGIN SELECT @PAR1 AS Par1, CASE @PAR2 WHEN 0 THEN 'Default' ELSE 'Other' END AS Par2 END GO 

Execution:

EXEC aaa_test 1 EXEC aaa_test 1,5 

You should not drop an object. Dropping an object suffers from two problems:

1) If the CREATE fails, you no longer have an object. (You can use transactions to avoid that, at the expense of a lot of boilerplate code)

2) You lose permissions on the object, if you do not explicitly re-create them.

I prefer to create a blank object within an "if not exists" condition, and then use ALTER, and have written helper procedures for that purpose.

Just to my extend previous answer.

Another reason why I prefer CREATE-ALTER over DROP-CREATE approach. It could lead to losing specifc properties about object. For example ExecIsStartup:

USE master GO CREATE TABLE dbo.silly_logging(id INT IDENTITY(1,1) PRIMARY KEY ,created_date DATETIME DEFAULT GETDATE() ,comment VARCHAR(100)); GO CREATE PROCEDURE dbo.my_procedure AS INSERT INTO dbo.silly_logging(comment) VALUES ('SQL Server Startup'); GO -- mark procedure to start at SQL Server instance startup EXEC sp_procoption @ProcName = 'dbo.my_procedure' , @OptionName = 'startup' , @OptionValue = 'on'; SELECT name, create_date, modify_date, is_auto_executed FROM master.sys.procedures WHERE is_auto_executed = 1; --name create_date modify_date is_auto_executed --my_procedure 2017-07-28 06:36:21.743 2017-07-28 06:36:24.513 1 

Now let's assume that someone wants to update this procedure using DROP-CREATE:

DROP PROCEDURE dbo.my_procedure; GO CREATE PROCEDURE dbo.my_procedure AS -- adding meaningless comment INSERT INTO dbo.silly_logging(comment) VALUES ('SQL Server Startup'); GO SELECT name, create_date, modify_date, is_auto_executed FROM master.sys.procedures WHERE is_auto_executed = 1; -- empty 

And if you are not aware of it or you don't check you will end up with procedure that won't start.

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaG1rZGaDcXvWoZitZZSkerq71GabqGWZo3q0vctmqp6qppq%2FbsDOZpqrnZGpsm670WaYpayVpw%3D%3D

 Share!