This month's T-SQL Tuesday, the 80th event, is being hosted by Chris Yates (blog | @YatesSQL). Today happens to be his birthday, so he approached the topic with ideas about gifts.
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:
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 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.
Speaking of permissions...
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):
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:
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? :-)).
What would be the bomb...
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:
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!
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.