#BackToBasics: Naming Stored Procedures
Back in January, I responded to an article on naming conventions that I found very sensible on balance, but with a few ideas I didn't quite agree with. I addressed most of my concerns in my previous post, entitled, "Subjectivity: Naming Standards."
However, I think I could have spent a little more time on naming conventions for stored procedures specifically, since this is an issue I've seen countless times in my career, and have seen several times since my earlier post.
There are two core issues when choosing a standard for procedure names (well, any standard, really):
- That the standard makes sense. I see all kinds of things done seemingly for no reason at all, or done in the wrong place -
sp_) prefixes, suffixes like
_proc, version numbers embedded in the names, and a handful of others. Once a procedure is connected to the API, middle tier, and application code, what you called it doesn't really matter - when it becomes important is when you go back to maintain or troubleshoot.
- That the standard is implemented consistently. This can be even worse. Quite honestly, the standard you choose is not really important - and not to sound cold, but I don't really care what standard you use. If it's not being used consistently, though, why bother having a convention at all? Many environments are like the wild west in this regard - too many cooks in the kitchen, and you can't tell if that's spaghetti sauce or cream of mushroom soup.
I've talked about this in my live sessions, but this is an extreme case that really happened - a team took over a week to fix a bug in a stored procedure, and the delay was caused solely by poor naming standards. What happened was that the application was calling
dbo.Customer_Update, but the team was hunting for the bug in a different procedure,
dbo.Update_Customer. While there was no formal convention in place, the real problem was inconsistency - a consultant charged with writing a different application didn't check for an existing procedure, she just looked for
dbo.Update_Customer in the list; when she didn't find it, she wrote her own. The bug itself wasn't crucial, but that lost time can never be recovered.
I'll repeat again that the convention you choose is largely irrelevant, as long as it makes sense to you and your team, and you all agree on it - and abide by it. But I am asked frequently for advice on naming conventions, and for things like tables, I'm not going to get into religious arguments about plural vs. singular, the dreaded
tbl prefix, or going to great lengths to avoid vowels. But I think I have a pretty sensible standard for stored procedures, and I am always happy to share my biases even though I know not everyone will agree with them. Again, I touched on this in my earlier post, but sometimes these things bear repeating and a little elaboration.
My preferred convention for procedure names is <Entity>_<Verb>.
As an example, if the stored procedure's purpose is to update the data for a customer, this would mean:
CREATE PROCEDURE dbo.Customer_Update
While a stored procedure used to generate a monthly sales report would mean:
CREATE PROCEDURE dbo.Customer_Update
The main reason behind my choice is productivity - if I need to find a stored procedure quickly, I'm probably going to use SSMS, and load either Object Explorer or Object Explorer Details. These views sort alphabetically, and have relatively clunky filter interfaces, so scanning the list is the fastest way to either identify an existing stored procedure or recognize the lack of one. It is much easier to find a procedure that updates a customer by scanning all the stored procedures that start with
Customer_ than it is to scan all of the stored procedures that start with
Update_. Even when I am not in a hurry, I think there is some tangible sensibility to grouping all of the stored procedures relating to the customer entity together, as opposed to grouping all of the stored procedures that perform updates together.
The extreme is when the conventions are all over the place and it really is the wild west where people just name procedures whatever they want. It's 3 AM, and you just got paged about a problem with customer updates. Would you rather be dealing with the database on the left, or one of the two on the right (click to enlarge):
Obviously that's a bit egregious; but the same mental delay that happens on the right can very easily happen if you're looking through a set of 300 stored procedures that all start with
Modify_. If they all start with
sp, that doesn't help much either, in any of the cases above - it just adds extra characters you have to parse and discard for every name. In fact I've never understood the desire to identify a procedure by prefixing it with
proc because I don't know of any context where the object name could be used and it wouldn't be obvious that it's a stored procedure.
Aaron (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.