Naming SQL Server Objects: Part II

When you pick a name for a SQL object what are you trying to do?  This article is Part II of a short serise about database object names.  Part I started with the structure of a name and discussed prefixes that distinguish different types of database objects such as stored procedures, user-defined functions, and tables.  This article is more about how to use the structure of the name to make it easier to find.

Making UDFs and stored procedures easier to find is paramount when creating their name. In the English language, naming functions with the group name followed by the action sounds somewhat unnatural.  It's more natural to put the verb first and name after that.  For Example GetSoundex sounds more natural then Text2Soundex.  But I have to ask, "How is starting 500 function names with Get ever going to help anyone find the function or even understand what it does no matter how natural it sounds?"   I've never heard a satisfactory answer to that question and so I've reversed the order of most names to maximize the ease of finding the function that the programmer is looking for.

Here is how I breakdown UDF names:

udf_<group><object><action><domain>

For stored procedures it's often but not always:

Usp_ or dba_ or dp_ or test_ <group><object><action>

Obviously  the udf_ prefix tells anyone that sees such a reference that they're looking at a User Defined Function.  That's only one of the possible prefixes that could be used for UDFs.  For system UDFs, Microsoft uses fn_.  Unless you're creating a system UDF, don't use the fn_ prefix.

I'm not convinced that the expenditure of four characters in every name is worth the extra typing involved, but for now, that's my convention. You might want to use something shorter or possibly no prefix. Is any prefix needed to make it obvious that a UDF is in use?

For stored procedures I have several prefixes including: usp_, dba_, test_, rpt_, and dp_.  Each designates a different group of procedures.  This differentiation helps to help with making the purpose clear.  Because the object name always appears in an EXEC statement it's obvious that the object is a stored procedure.

The <group> part of the name pulls functions or procedures together with other objects in the same part of an application.  The <object> is what's being worked on.  The <action> is what's being done to the <object> and finally <domain> is used with scalar functions to indicate something about the returned result.  Not so much the datatype as the units.  So udf_Job_RuntimeSEC is reporting in seconds.

When naming UDFs I have a number of generic groups.  I also add groups for functions associated with a particular part of the application.  Some of these might be "Quote, Contract, or Employees.  Table 3 has some groups that I use in naming UDFs.

                                 Table 3 Some UDF Groups

For dba_ stored procedures some groups are: Disk, Perf, ErrorLog, Permission, and Job. 

So here are some typical names and a breakdown of the name.

As you can see, every name doesn't follow all the rules exactly. The rules are bent in order to convey the meaning of the database object, which is the original purpose of the name.

In addition to the prefixes that I use indicate the type of object there are three additional prefixes that designate something about the stage of development for the object.  They are:

                             Table 5 Lifestage Designators

adhoc_ objects are intended for some one-time only purpose.  For example, when loading test data, I might employ an adhoc_ table for staging the data from an excel spreadsheet before I insert it into the database tables.  This lets me separate the loading process, which is somewhat error prone, from the process of inserting the data into its destination using an INSERT.  Of course, some adhoc_ objects might stick around.  If they do, remove the adhoc_ pre-prefix.

The wip_ designator for Work In Progress tells other developers that the code isn't complete.  Don't count on any wip_ code to work.  wip_ should only be for development.  wip_ objects shouldn't make their way into your production database. 

I don’t really like the idea of deleting code.  I always think that there’s going to be a good reason why I’m going to need it again.  At the same time I like to keep my schema’s pretty clean and uncluttered by code or tables that aren’t used or even that might be harmful if used.  While SourceSafe or other code management tools might be a good solution to keeping old versions around, if the object's name isn't changed it continues to appear to be useful even if it isn't.  My solution is to rename any obsolete database objects with a prefix of “obs_”.  This applies to tables, views, stored procedures, and UDFs.  This has the advantage that the objects remain in the database and can be searched if desired but it’s obvious, at least to me, that “obs_” objects shouldn’t be used.  Nor do they need to be included in upgrade scripts.

That's all I have to say about naming database objects.  Explanation and ease of location are the two most important features of an object's name.  I know that there are many naming conventions and that the most important thing is having one and following it.  Even a mediocre convention that's followed is better than no convention at all.

Transact-SQL User-Defined Functions has been published!  Take a look at it now!

Do you have an interesting stored procedure, user-defined-function, or other sample code?  If you'd like to share them please send them in an email to: coding-in-sql@NovickSoftware.com  and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andy
Andrew Novick