SQL Server Coding Standards - Part 1

Introduction

Everyone needs standards. They promote cleaner code, better programming, and make it

easier for teams of people to work together. Standards are one of the foundations upon

which computer science is built. Coding standards (as I define them) are the standards which

specify how the object source code is written. This includes the format, documentation, structure

of the object code, etc. Having good coding standards ensures that all object source code looks

similar and allows different members of a team to more quickly understand the object.

In my career I have worked in a number of different programming environments and

have seen different types of standards for developing code. These have ranged from free (read

non-existent here) to extremely rigid (variable names listed in a central document and

specific formats for writing code). There have not been too many that I have really liked,

and many of them were cumbersome. One thing that I have usually found, however, is that almost

never are there DBA standards. I've been guilty of this myself, when I managed two other DBAs, where

I had not formally spelled out how object code was written.

In my current job, we recently had doubled the size of the development staff and completed

a large project. During the lull that followed, a couple developers were assigned the task of

developing coding standards to ensure that all development efforts would look alike. The resulting

work (developed for Cold Fusion source code) was a document and a style that I really liked. After

viewing this document, I started to develop my own document based on this guide.

What Types of Standards Are Included?

In developing my own coding standards, I decided to include the following items as they pertain to

SQL source code. This would not apply to embedded SQL code which often exists in our Cold Fusion site (as

well as many web sites. Here is the list of items that are covered:

• Object Headers
• Variable Names
• Source Code Spacing
• Source Code Formatting
• Query Formatting
• Object Footers

The Standards

What about SQL Server development standards? I think they should exist as well. A few jobs ago, I ran into

issues with two other developers in examining each other's server code. We found that we were not only using

different standards for layout, but also for accessing objects in transactions. Luckily we caught this before

any deadlocks occurred in our system, but it presented the need for implementing some type of standards.

Over the last few years, I have continually developed and evolved my set of coding standards. Today, they

exist as a document with my current company, but I still examine them at times to see if they are truly worthy

of being "standards". After all, I implemented them (after some thought) because I needed them, not because I

had the best solution for each category.

There are really two types of standards: coding standards and naming standards. This article will deal with

the naming standards and part 2 will discuss coding standards. Here is a current list of my standards:

• Databases

  Each database on a server should be named using a name that is logical and applicable to the use

  of the database. Since third party databases often require specific names, this specification

  cannot give more concrete examples of naming standards. If you are building software which may be deployed on another server, you may wish to prefix the database name with some acronym signifying your company, as in example 3.

  Examples:

  • Sales
  • Dynamics
  • IBM_SalesAnalysis
• Backup Devices (Full Backup)

  Any file that contains a complete backup should be named in the following format:

  <database name>_<4 digit year><month><day><hour><minute><second>

  where all times are the time the backup was started. The extension for all full backup files

  should be ".bak". All items should include leading zeros for values whose size is less than the

  size of the maximum value, i.e. always include a 2 digit month.

  Examples:

  • Sales_20011015080000.bak
  • Dynamics_20010908000000.bak
• Backup Devices (Differential Backup)

  Any file that contains a differential backup should be named in the following format:

  <database name>_<4 digit year><month><day><hour><minute><second>

  where all times are the time the backup was started. The extension for all full backup files

  should be ".dif". All items should include leading zeros for values whose size is less than the

  size of the maximum value, i.e. always include a 2 digit month.

  Examples:

  • Sales_20011015083000.dif
  • Dynamics_20010908120000.dif
• Backup Devices (Transaction Log Backup)

  Any file that contains a transaction log backup should be named in the following format:

  <database name>_<4 digit year><month><day><hour><minute><second>

  where all times are the time the backup was started. The extension for all full backup files

  should be ".trn". All items should include leading zeros for values whose size is less than the

  size of the maximum value, i.e. always include a 2 digit month.

  Examples:

  • Sales_20011015081500.trn
  • Dynamics_20010908080000.trn
• Logins

  All login names should follow the company standards for network login names. Currently the

  standard is:

  <first initial>_<last name><middle initial (if needed)>

  Examples:

  • sjones
  • bknight
• Users

  All database user names should match the login name to which it is mapped. NO User accounts

  should be shared among multiple logins. Use roles instead.

  Examples:

  • sjones
  • bknight
• Roles

  All database roles should be named for the function of the role. This may be the name

  of the department or the job function.

  Examples:

  • Marketing
  • PurchasingAgents
• Tables

  All tables should be named for the function of the table. For multiple word tables, the

  name should be in proper case for each word. No spaces should be used in table names.

  Examples:

  • Orders
  • OrderLineItems
• Columns

  Columns used in either tables or views should follow the same naming convention as

  for tables. Proper case all words with no spaces inside the name.

  Examples:

  • OrderID
  • ProductCode
  • QuantityPurchased
• Views

  All view names should begin with a lower case "v" and then follow the same naming

  conventions as for a table. Proper case all words in the name with no internal spaces. If this is a view of a single table and contains all fields, then use "v" plus the table name.

  Examples:

  • vOrderDetails
  • vProduct
• Indexes

  All indexes should be named in the following format:

  <Table name>_<index type><index number (optional)>

  where the table name matches the table or view to which the index is being

  applied. The index types are:

  Primary Key - PK

  Clustered Index - IDX

  Nonclustered Index - NDX

  Only when there is more than one nonclustered index should the index numbering be used.

  Examples:

  • Orders_PK
  • Products_IDX
  • ProductDetails_NDX
  • ProductDetails_NDX2
• Triggers

  All triggers should contain the name of the table, an underscore followed by "tr" and the letters which represent the intention of the trigger (i for insert, u for update, d for delete). If there are more than one trigger, a numerical designation, starting with 2 should be appended to the name.

  Examples:

  • Customers_tri
  • Orders_triu
  • Products_trd
  • Products_trd2
• User Defined Functions (UDFs)

  A user defined function should prefixed by "udf_" and then a description that logically follows the function process. The description should be proper case words with no spaces.

  Examples:

  • udf_GetNextID
  • udf_SumOrderLines
• Defaults

  All defaults should be prefixed with "df_" and then some description of the default value. The description should be proper case with no spaces or underscores.

  Examples:

  • df_One
  • df_GetDate

Conclusion

I hope that all of you out there have some type of coding standard to which you adhere. I think

most of us naturally develop one over time, but if you need to work with other individuals, it

makes sense to formalize your standards. Feel free to adopt my coding standards if you need them and

I would be interested in hearing about any enhancements you make or disagreements you have.

As always I welcome feedback in the forum below and please vote your opinion on this article.

Steve Jones

©July 2001