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 ALTER keyword.
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.
USE master;
GO
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''');
GO
...
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 ALTER …
(Side note: I spent a good few minutes one day a few years ago not remembering the ALTER keyword.)
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:
USE master;
GO
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.