Over the last two years, as I’ve been working more closely on our application database (a database with nearly 20 years of history), one simple question keeps coming up as I work on old scripts and prepare new changes.
Should we use a DROP/CREATE or CREATE/ALTER pattern for Stored Procedures, Functions, Views, and Triggers?
Because of the tooling we had used previously, every script I had worked with consistently used the DROP/CREATE pattern when doing updates. For example:
IF OBJECT_ID('dbo.Proc123', 'P') IS NOT NULL
DROP PROC dbo.Proc123
GO
CREATE PROC dbo.Proc123
...
GO
GRANT EXECUTE ---
GO
And I never questioned it. When you manage hundreds of client databases that all share the same schema and send updates multiple times a year to on-premise clients, it was just easier to make sure things were cleaned up and then create the PROC/Trigger/etc. again.
But then I started seeing a new pattern from a number of DBAs and teachers. I think I first noticed it with Adam Machanic’s sp_WhoIsActive and soon after with Brent Ozar’s sp_Blitz family of scripts. They used a different approach similar to this:
IF OBJECT_ID('dbo.Proc123', 'P') IS NULL
EXEC ('CREATE PROC dbo.Proc123 AS SELECT ''stub version, to be replaced''')
GO
ALTER PROC dbo.Proc123
...
GO
GRANT EXECUTE ---
GO
Knowing these folks to be particularly smart in many things SQL, I figured there was a reason, although my Google-Foo only turned up a few opinions. There was, as best as I could tell, no hard and fast rule for what was ‘right’.
In most cases, the reason most people tended towards the CREATE/ALTER approach was to retain any previously set GRANT’s on the object. That makes a lot of sense and is compelling enough for me to start switching.
But then, during Erin Stellato‘s PASS 2017 Pre-Con on Query Store, she mentioned the most compelling reason I’ve heard thus far. It’s also something that I’m sure translates into other DMV-like tuning data in versions prior to SQL 2016 that don’t have query store.
Query Store works by tracking the identity of an object (Object_ID) and the performance metrics related to that object. If you are working to tune something like a PROC and want to track its performance in Query Store, you’ll be shooting yourself in the foot if you DROP/CREATE the PROC each time because it will get a new Object_ID and the results of your changes won’t be tied to the previous metrics in the plan cache. It turns out that this is also mentioned as a best practice in the Query Store documentation.
While this should seem obvious once you hear it, this alone was a compelling reason to switch our processes and begin using CREATE/ALTER instead.
One final note
There is a new feature in SQL 2016 CP3, called ‘CREATE OR ALTER’. It allows you to ask the SQL engine to do the CREATE/ALTER logic for you so that you don’t have to write the CREATE check first when rolling out changes. Unfortunately we support versions prior to SQL 2016 so we cannot yet move to make this our default pattern. Someday!