Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Coding Standards - Part 1

By Steve Jones, (first published: 2002/05/09)

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
Total article views: 47140 | Views in the last 30 days: 46
 
Related Articles
FORUM

T-SQL Development Standards

Writing corporate development standards

FORUM

upgrade SQL Server 2005 developer ed to standard ed

upgrade SQL Server 2005 developer ed to standard ed

ARTICLE

An Example of Test-Driven Development

Developing a database can be an trying experience, and it's ways good to see how someone else builds...

ARTICLE

Exploring Recursive CTEs by Example

Recursive CTEs can be confusing and scary, so examining some non-standard examples may cast light up...

FORUM

Restore DB backup from Enterprise Edition to Standard Edition

Restore DB backup from Enterprise Edition to Standard Edition

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones