Naming SQL Server Objects: Part I

This is Part I of a two part article.  Part II is in next week's newsletter.

When you pick a name for a SQL object what are you trying to do?  When I name an object I want several things to happen. I want the programmer who sees the object to know:

• The type of object.
• That the object is a user object as opposed to a system object.
• Something about the object's purpose.
• If it's a function or procedure, how to use the results of the object.

Very importantly, I also want any programmer working on a project with me or after me to be able to find the object after it's written.  Having two programmers create the same function, procedure or view, or doing without the benefits of an already created object is a productivity loss.  If a way can be found to eliminate that loss everyone is happier: management, the programmer who found a useful object, and me.

However, I don't want to write any programming manuals.  Not only don't I want to spend my time writing program documentation, my clients don't want to pay me to do it. Everything needed for finding the correct object and deciding if it's applicable to the situation needs to be in SQL Server.  That's why I include moderately large header comments in stored procedures, user-defined functions, and views.  It's also why I use SQL Enterprise Manager's capability to use extended properties to document tables and table columns.  Normally there isn't going to be any external documentation unless I can figure out how to write a program that creates it from information already in the database.

The name is the most important part of the database object that helps it to be found.  Some of my ideas are influenced by long-term exposure to Intellisense in Visual Studio.  If you're not familiar with it, it's the technology that Microsoft has built into their Visual Studio Integrated Development Environment (IDE) that helps complete the names of variables by displaying a list of known variables and types that are in scope and that start with the characters that you typed.   Sadly, Intellisense isn't part of SQL Server 2000 but it will be part of SQL Server 2005 (aka. Yukon).  That's something to look forward to.

Figure 1. is a picture of how Intellisense shows the list of functions available while editing a small Visual Basic 6 program.  While editing I typed three characters Tex and then pressed CTRL-Space.  That brings up a list of all the names in scope that begin with "Tex".  These include all of my library functions in the Text group and the TextBox group.  I can then scroll up or down to find the function that I'm interested in.

Figure 1 Intellisense at Work in a Visual Basic Program.

I generally follow the following format when naming an object:

• Start with an object type designator.
• Follow with a group name
• Add a qualifier that distinguishes the object from the rest of the group.
• If needed add additional qualifiers.

So let's start by designating the type of object with the object type designator. Table 1 shows some of the designators that I use and their meaning.   

                             Table  1 Object Type Prefixes

I don't usually use a designator for tables.  I know that many database designers use "tbl" as a prefix for tables but I've decided that it's unnecessary.  The "v" on views is enough to distinguish views from tables.  They're to two objects types that are most likely to be confused because they're used in the same places in SQL syntax.

So a name for a stored procedure might be usp_Quote_Sync_Master.  Quote is the group.  Sync distingishes a subgroup within Quote and Master is the final qualifier.

Capitalization or underscores or both can be used to separate parts of the name for each other.  My choice usually depends on local conventions at the client site.  When I have my way, I use a mixture depending on the object.  Table 2 has a couple of examples of naming a user stored procedure from the Sync function Quote module:

                                Table 2 Alternative Name Spacing

We're just getting started.  In Part II of this article, I'll take a look at putting together the parts of a name and show plenty of examples of how my naming strategy works.

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