Having a good set of naming conventions for your SQL Server objects is one of the most vital things to a company. In the long duration of a business, it saves money and time as programmers are transferred internally and don't need to relearn object names. As learning curves lower, cost lowers. This article covers some of the conventions I use and why I use them.

If you put 20 DBAs in one room, you're bound to come out with 20 different standards. In the long run, all that matters is you have a well-defined standard, whatever it may be. I receive more hate mail about this article than anything I've ever written. Keep in mind that the standards that I document here are based on the lowest common denominator between all database systems. If you don't have to have any portability between versions, then many of these standards are not needed. I hate doing some of these myself but I needed to make sure I could port a database to Oracle and DB2

Documentation

As you come up with company or enterprise standards, it is of utmost importance to document them. After all, what good are standards if no one knows about them? Make sure your documents are posted in a Lotus Notes database or on the Intranet somewhere.

Table Names

The most important thing to keep in mind when it comes to naming any SQL Server object is that your business needs may change down the road. You may be thinking your application has a relatively small backend but your next release may include some type of auditing, which makes your database grow 100 fold. It is this type of unpredictability that I generally chose the lowest common denominator. For example, Oracle and DB2 both have restrictions that will make your portability to them quite expensive if you don't take the lowest common denominator of both of the database systems. It would be nice if your application could always be on SQL Server, but as politics move in your company, so may the database. I'm sure many of you have cringed at the site of a database vendor taking your boss out to a golf game.

With that in mind, there are two compatibility issues with DB2 and Oracle that must be addressed. The current version of DB2 only allows for 18 characters. In a future release slated for this winter, it will support 30 characters. I chose to play it safe with 18 characters knowing how slow most companies move. Oracle supports only upper case table and element names easily. If Oracle is even on your radar, make sure your SQL Server table names and field names are always in upper case. If you issue a create table statement in Oracle, Oracle will convert it all to upper case however, some of the migration utilities do not give you this luxury.

If you have application components such as a accounting system that you know you're going to integrate with other applications, you will need to prefix those components for easy migration. For example, if you have a workflow system that you use in 8 different applications, prefix those tables with the two letter abbreviation WF (WF_QUEUE). If you do this, you can easily find the tables in Enterprise Manager. At the table level, avoid using abbreviations in other cases.

Column Names

Field names again should be all upper case. They should be descriptive to their purpose and only use abbreviations for a select few names. I have a list of 24 abbreviations that I use on a regular basis (NAME=NM, DESCRIPTION=DE). Anything else, I use the entire word. Use underscores to separate words.

Stored Procedures

The same rules apply here for length of fields. Also if your developing a component to an application versus the entire application, prefix the stored procedure name with a 2 letter prefix. I begin my stored procedures with SP__ (2 underscores) and then the component prefix. You can reduce the learning curve tremendously if you use descriptive names and if your name denotes whether your stored procedure selects, inserts, updates or deletes data.

Examples:

• SP__WFSELECTBORROWER (WF is the workflow system)

• SP__UPDATEPAYEESTATUS

• SP_APINSERTBILLRECORD (AP is the Accounts Payable system)

Triggers

Triggers like stored procedures should designate what happens to the data (i = insert ,u = update ,d =delete). I begin all triggers with the letter T and the table name. For example a trigger that inserts into the payable table would be TI_PAYABLE.

Databases

In the service industry where you may be mass-producing applications, a debate usually begins when the decision must be made to go with one database per client or a shared database. This really is a political decision more than anything. Once you make the decision, stick to it though. My general rule of thumb is to have a separate database for each client for anything that contains financial or client-level data. Data that can be shared such as code tables or forms I have a shared database. This allows you the luxury of taking individual clients down versus an entire system and you can rest assure at night that a programmer didn't forget his WHERE ClientID=XXX clause, allowing Client A to see his competitor's data.