T-SQL Tuesday: CREATE or REPLACE
He had several suggestions for our posts, and one that stood out to me aimed at features we wished weren't missing from SQL Server:
Perhaps there is a feature you would like to see added into SQL that just isn’t there yet – what is it?
While I feel I may have used up my wish list in last month's T-SQL Tuesday, I could probably handle it if this were the topic every month, because there are a lot of things I'd love to see added to our favorite database platform.
One specific feature request, though (which was re-invigorated when SQL Server 2016 added
DROP IF EXISTS), is the undoubtedly more useful
CREATE OR REPLACE.
DROP IF EXISTS can be very useful when you just want to write a simple
CREATE PROCEDURE, for example, and discard anything that existed before. The way we would do this in the past would be to check for the object's existence, drop it if it existed (which in some cases can require dynamic SQL), and then go ahead and re-create:
IF EXISTS ( SELECT 1 FROM sys.procedures WHERE [schema_id] = 1 AND name = N'MyProcedure' ) BEGIN DROP PROCEDURE dbo.MyProcedure; END GO CREATE PROCEDURE dbo.MyProcedure AS -- ... the good stuff ...
DROP IF EXISTS, I can now do this slightly less awkwardly, without the
IF check and without any need for dynamic SQL:
DROP IF EXISTS PROCEDURE dbo.MyProcedure; GO CREATE PROCEDURE dbo.MyProcedure AS -- ... the good stuff ...
DROP IF EXISTS is certainly cleaner, both in logic and in sheer code size. Please note, though, that
DROP IF EXISTS can "fail" by hiding a different, permissions-related issue.
DROP IF EXISTS doesn't solve an underlying issue with all of the above approaches: permissions. If I have set up elaborate permissions on an object, I don't want to just throw that away and create it from scratch. So what do people do now? Well, typically, the opposite of the above, either they script a conditional
ALTER, which has to be done in dynamic SQL (because
ALTER PROCEDURE has to be in its own batch):
DECLARE @sql nvarchar(max) = N' PROCEDURE dbo.MyProcedure AS -- ... the good stuff ...'; IF EXISTS ( SELECT 1 FROM sys.procedures ... ) BEGIN -- let's ALTER so we don't lose permissions SET @sql = N'ALTER ' + @sql; EXEC sys.sp_executesql @sql; END ELSE BEGIN -- doesn't exist, let's create it: SET @sql = N'CREATE ' + @sql; EXEC sys.sp_executesql @sql; END
Or something a little more clever: creating an empty shell of a procedure if it doesn't already exist, and skipping directly to the
ALTER if it does. This allows the body of the procedure to be modified and maintained in source control without the complications of dynamic SQL:
IF NOT EXISTS ( SELECT 1 FROM sys.procedures ... ) BEGIN -- create a shadow of a procedure EXEC sys.sp_executesql N'CREATE PROCEDURE dbo.MyProcedure AS PRINT 1;'; END GO ALTER PROCEDURE dbo.MyProcedure AS -- ... the good stuff ...
That solves the problem, but it is not very tidy, and it requires that extra scaffolding accompany the procedure body in source control (or wherever you keep your database scripts - because you keep your database scripts somewhere, right? :-)).
Yeah, I've probably dated myself there, but the ultimate solution would be inline syntax similar to
DROP IF EXISTS, that creates the procedure if it doesn't already exist, and alters it if it does:
CREATE_OR_ALTER PROCEDURE dbo.MyProcedure AS -- ... the good stuff ...
This would simplify the deployment of these objects, prevent trampling of permissions, and make a single, self-contained statement that could be stored in source control and not require any extra handling or dynamic SQL. Would you like to see this in T-SQL? Okay, go vote and comment!
I tried to promote this idea six years ago, though that specific suggestion I pointed to was swept under the rug. Also, back in 2008, Microsoft said, "This feature is near the top of our todo list for the upcoming release." I wonder what ever happened to that "to do" list.
Of course, this wouldn't make much sense for non-module objects, like tables, since their DDL is vastly more complex and often couldn't often be accomplished in a single statement anyway. I would love to see it for procedures, functions, views, and triggers, as a start.