Standards Part 2 – Naming Objects

Introduction

Everyone has naming standards in their company. If they do not, they should. It makes
everything easier for all employees. Developers learned long ago that having naming standards
in place makes it easier to train new developers, troubleshoot code, and reduces errors. The
same holds true for SQL Servers. If something is standardized, then you get some amount of
information just from the name. That may help you find an object quicker, understand the object’s
purpose, or just keep everything more organized.

When I started managing SQL Servers, I quickly needed to develop standards to ensure that other
developers and I could work with each others’ code. I searched and adapted a set of standards from a
few different sources to meet my needs. What I had hoped to find was a set of standards that someone
else had tried and just use them. So, here are the ones I have come up with. Please feel free to use these
in your company or change them to meet your needs.

The following are standards that I have developed and
evolved over the years. Feel free to use them if you like or come up with your
own.

Database Devices or files:

Devices no longer apply to
SQL Server v7.0 and above, but there are still physical files which should
follow this convention.

Database devices and files shall
be named for the name of the database that is associated with them. The format
for the file name should be the following:

<database>_<purpose><sequence>.<extension>

There are three types of files
in SQL Server 7 and above: data files, system files, and log files. By default,
system and data files are combined into the first data file. If there is more
than a single data file, the sequence should be a numerical differentiator
between the files. The extensions are determined as follows:

First data file: .mdf

Subsequent data files:.ndf

Log files: .ldf

The name shall be in proper case, except where an acronym is being used.

Filename Examples:

• Iqd_dev_data.mdf
• Master_data.mdf
• QA_log.ldf
• DBA_Sys.mdf
• DBA_Data.ndf

Backup Devices:

Backup devices are to be named in the following manner:

<Server>_<database>_<timestamp – Optional>.<type>

where

server: name of the server. If a central backup server is ever used, then this will
be needed.

Database: name of the database in the same format as the database is named (same case,
etc).

Time: timestamp for the backup. Optional.

Type: .bak for full backups, .dif for differentials, .trn for incrementals.

Backup File Examples:

• Morpheus_master.BAK
  full backup of Morpheus..master
• ProdSQL_Dynamics.dif
  differential backup of the Dynamics database on the ProdSQL server.
• ProdSQL_Dynamics.trn
  transaction log backup (incremental) of the Dynamics database on the ProdSQL
  server.

Databases:

User databases are to be named
for the application or purpose they server. Proper case for database names is
suggested, but many applications may create their own database using Upper
case.

Example:

• Training
• GMSM
• master
• QA

Logins:

Logins shall consist of the first letter of the person’s first name
and the full last name. If there are collisions, then the first initial of
first name, first initial of middle name can be used. If this is not
sufficient, then begin spelling out the first name and then the middle name
until the collision is resolved. All logins shall consist of lower case
letters. The exceptions to this are any system level or administrative logins.

Login Examples:

• sjones
• bsmith
• jqpublic
• Accounting

Users:

Users in each database should be named the same as the
matching login. At this time, no reason has been given to deviate from this for
any reason.

Tables:

Tables should be named for the
type of data that they are holding in accordance with the business lexicon used
by the company. This prevents developers from confusing or being confused when
working with business people. Abbreviations for words should conform to the
list of abbreviations that has been declared the standard for the company.
Tables that link other tables together should have a suffix of Lx, where x is a
numerical indicator. This allows multiple independent links between tables.
Lookup tables that contain the PK and another duplicate field in them to speed
lookups should be suffixed by a _lu. Proper case names should be used for
names.

Table Name Examples:

• Member
• Customer
• Product
• Product_lu

Columns:

Columns shall be named with
words describing their purpose.  Abbreviations should be used where
possible and in accordance with the standard abbreviation list. Proper case all
words in the name except acronyms which should be upper case.. Underscores
shall be added for those columns needing to designate units.  The
designations for units shall conform to the engineering standards.

Column Name Examples:

• EmpID
• FrstNm
• LastNm
• Velocity_mpm
• Mass_kg
• ProdSKU

Indexes:

Indexes shall be named for the
table they are attached to and the purpose of the index. All letters shall be
lower in case. Primary keys shall have a suffix of _PK. Foreign keys shall have
a suffix of _FKx where x is a number. The number shall simply be incremental.
Clustered indexes shall have a suffix of _IDX. All other indexes shall have a
suffix of _NDXx where x is an incremental number. Only one suffix per index
shall be appended. The application of the appropriate suffix shall follow the
following hierarchy: primary key, clustered index, foreign key, other index.
For example an index that is both a primary key and is clustered shall have a
suffix of _PK.

Example:

• employee_PK
• customer_IDX
• employee_FK1
• employee_NDX1
• employee_NDX2

Triggers:

Triggers shall be named by the
table they are for and also for the type of trigger. All letters shall be lower
in case. The purpose of the trigger shall be the prefix to the name. All
triggers shall start with the letter t, a letter(s) designating the type, an
underscore, and the table name. The type shall be designated as i = insert ,u =
update ,d = delete.

Example:

Defaults:

Defaults shall be named by a df
and a description of what the default does. All words should be run together
and not separated by underscores. The description should be proper case

Example:

Rules:

Rules shall be named by an r_
and a description of what the rule does. All words should be run together and
not be separated by an underscores. Abbreviations and proper case should be
used.

Rule Examples:

• r_onlynumbers
• r_nospaces

User Defined Datatypes:

User defined datatypes should
not be used at this time. The potential to limit flexibility and cause a large
maintenance issue is too great. Until the IT department modifies this section,
User Defined Datatypes should not be used.

Stored Procedures:

System level stored procedures
shall be named by sp__ (two underscores) and a description of what the stored
procedure does.

All application level stored
procedures shall follow a set prefix in place of the sp with a description of
what the stored procedure does. The following standards are in place for
these types of procedures:

• spIns<table name> – Insert procedure for all columns for the table, except identity
  column.
• spDel<table name) – requires primary key and removes the specified row from the table
• spSel<table name> – Returns data from a single table or view. Takes the primary key
  as a parameter (optional). If sent, then the matching row is returned. If
  no parameter sent, then all rows are returned.
• spUpd<table
  name> – takes primary key as parameters and then all columns in order
  that they appear in the table. If a NULL is sent for a parameter, then
  that column is not updated.
• spGet<description>
  – select procedure returning data from joined tables
• spTot<description>
  – returns an aggregate of some type. 
• spSet<table><column>
  – sets a specific column value. A specialized procedure to set a value for
  a specific business purpose.
• spInc<table><column>
  – increments the column value
• spFix<prupose> – procedure to scrub or
  correct data that may be incorrect.
• spSend<purpose> – Stored procedure that
  sends a message to someone. This could be email, broadcast, http post,
  etc.
• dbsp<purpose> – DBA stored procedure to
  perform administrative or maintenance tasks. NOT FOR USE by applications.
  Permissions are not necessarily set for non-sa level logins on objects
  used in these procedures.

Abbreviations
should be used where appropriate. A list of abbreviations is available at: abbreviations.xls

Example:

• sp__LoadData
• spChkTime
• spGetEmpRpt
• spInsCustomer
• spDelOrder

Alerts:

Alerts shall be named with the
database in UPPER CASE, a description of what the alert is for, and what level of
alert it is.

Alert Example:

• CUSTOMER Fatal Errors 19

Tasks and Jobs:

Tasks shall be named with the
database, the frequency or time (in 24hr format) a description of what the task
is, the name of the procedure, or the name of the DTS task being run.

Task Example:

• ALL
  DATABASES Daily 0100 synch of development server Admin
• PRODUCTION Sun 0400 DBCC checktable
• Dynamics MTWRF 0500 Import_Sales_Flat_File

Conclusions

I think standards are very important to having a well
organized development team and encourage you to install some if you do not have
them. If you have standards, then be sure they are enforced and that they grow
and change as needed. For other points of view on this topic, both
Michael
Hotek and
Brian Knight have standards articles as well on Swynk.