For the longest time, T-SQL writers have had to wrestle with ways of testing for an object’s existence so that it can either be dropped and recreated, or modified as needed.
Last week we covered the new
DROP ... IF EXISTS syntax. This week goes into how we handle changes to objects.
We’ve spent many hours of our lives fighting with an object existence check so that
ALTER commands don’t fail.
The common implementation pattern now is to
CREATE a dummy object with the appropriate name and then use
ALTER to write the actual code. That way, future changes can just be done by using the
We can see this in a famous example: sp_WhoIsActive, an extremely popular stored procedure written by Adam Machanic, which I highly recommend installing when setting up a new SQL Server instance.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'sp_WhoIsActive')
EXEC ('CREATE PROC dbo.sp_WhoIsActive AS
SELECT ''stub version, to be replaced''');
ALTER PROC dbo.sp_WhoIsActive (...);
Look at all that unnecessary code. It’s messy; it’s prone to errors. There are so many ways to do it, which makes it inconsistent, meaning that automatically searching through a code base isn’t reliable. Additionally, if we miss a single quotation mark, the entire script fails.
In a word: Ugh!
What happens if we forget to run the stub first? What happens if we have an existing object and run the
CREATE accidentally, then we have to manually change it to an
(Side note: I spent a good few minutes one day a few years ago not remembering the
SQL Server 2016 Service Pack 1 has finally added in a feature that many DBAs and database developers have been clamouring for:
CREATE OR ALTER.
In the above example, the entire
IF NOT EXISTS section can be replaced with:
CREATE OR ALTER PROC dbo.sp_WhoIsActive (...);
While this has been a long time coming, causing many anguished cries from people writing T-SQL scripts over the years, we are going to love using this new, small, yet significant, syntax.
Share your best
ALTER story with me on Twitter, at @bornsql.