Database Coding Standard and Guideline

Author's note: A surprising number of people still looking at this document. Very Cool! Thanks! There is a considerable amount of updated information available on this subject at www.bwunder.com There is also a bit of stuff below I never really agreed with. The point was to show an example of a coding standard that was created by the team. This is such an example. A long one at that. One of my goals for 2008 is to update the standard for SQL Server 2008 and eliminate all the stuff I do not agree with. The update will be posted on www.bwunder.com. Let me know what you'd like to see in there! (email). Also note that there are over 100 articles and many times that number of scripts for SQL Server posted on this site - ?bill

Summary

This document will provide a framework to aid in optimal usability of Microsoft SQL Server 7.0 schema, scripts and stored procedures developed for applications by defining a reasonable, consistent and effective coding style. The qualities of usability as used here include readability by other database technicians, ease of implementation, maintainability, and consistency. The framework will serve to improve the application without unnecessary impact on development and unnecessary controls on personal coding preferences. For these reasons the framework will focus on identifier naming conventions that are intended to be used by all developers, general style guidelines indicating the preferred format and usage of SQL language components, and a definition of the database development methodology.

The identifier segment of the standard will formalize naming conventions. All schema, scripts and stored procedures will conform to all elements of this area of the document.

The general guideline will include SQL statement formatting and outlines for solutions to more complex components within scripts and stored procedures. This instrument is intended as a ‘best practice’ model consistent with the identified architecture and requirements. It may be necessary to adapt the guideline to specific solutions within the application. While the guidelines are not mandatory, adherence will aid in the ultimate success of the application and ease of maintenance. All developers will be expected to reasonably defend any variance from the guideline.

The combination of conformance to standards and development within the stated guidelines will be measured and assessed in the context of the methodology. The methodology will present structure and consistency through clearly defined requirement specifications, change control procedures, code review, testing, controlled iterative development cycles, and regular developer evaluations.

There is a perceptive risk in not defining a ubiquitous and mandatory coding standard. Stored procedure and script development may become sloppy and unreadable resulting in diminished usability. In actuality, this is a deficiency in the developer and/or the development methodology not the framework. Without the controls of testing, review and feedback to support a high quality development team any standard will not assure usability. Defining most issues of style and layout within the context of guidelines rather than dogma frees the developer to produce robust and creative solutions. This latitude in the framework is intended to balance mechanics and creativity. Clear definition and execution of the controls will assure the purpose of this or any coding standard.

Identifiers

• Case
• Use all upper case for table and view names
• Use mixed case for column names and variables
• Use mixed case for stored procedure name
• Use lower case for other names except use the same case as indicated above where a table or column is used in another object’s name

• Prefixes and suffixes:
• Use the following standard prefixes for database objects:

Object type	Prefix	Example
Primary key Clustered	pkc_	pkc_MY_TABLE__Column
Primary key Nonclustered	pkn_	pkn_TB_TABLE__Column_List
Index Clustered	ixc_	ixc_TS2_TABLE__Column
Index Nonclustered	ixn_	ixn_TB_TABLE__Column_List
Foreign key	fk_	fk_THIS_TABLE__ColumnB__to__TB_PKEY_TABLE__ColumnA
Unique Constraint	unq_	unq_TB_TABLE__Column_List
Check Constraint	chk_	chk_TB_TABLE__Column
Column Default	dft_	dft_TB_TABLE_Column_List
Passed Parameter	@p	@pPassedVariableName
Local Variable	@	@VariableName
Table	TB_, *_	TB_TABLE_NAME (see detail below)
View	VW_	VW_NET_ACTIVE_UNITS
User Defined Scalar Function	ufs_	ufs_return_value_name
User Defined Table Function	uft_	uft_TB_TABLE_NAME
Stored Procedure	*	Eds_Def (see detail below)

• Use the following standard prefixes for scripts:

Script type	Prefix	Example
Stored procedure script	proc_	proc_Calendar.sql
Schema script	def_	def_Calender.sql
Conversion script	conv_	conv_Schedule.sql
Rollback script	rbk_	rbk_Schedule.sql

• Save all scripts using the .sql extension
• Use the following standard column and variable suffix abbreviations:

Object Type	Suffix	Example
Account	_Acct	Process_Corp_Acct
Address	_Addr	Contact_Addr
Amount	_Amt	Total_Credit_Amt
Balance	_Bal	Available_Bal
Date or Datetime	_Dt	Active_Dt, @Archive_Dt
Description	_Desc	Product_Desc
Date of Birth	_DOB	Alternate_DOB
Indicator	_Ind	Net_Gross_Ind
Line(n)	_Ln(n)	Address_Ln2, @pOrderLn
Number	_Nbr	Bank_Nbr
Record identifier/identity	_Id	Entity_Id, @pEntityId
Tax Id Number	_TIN	Merchant_TIN
Card Transactions	_Trans	Daily_Nbr_Trans
Zip Code	_Zip	Recipient_Zip

• All other suffixes should be whole words

Name, Type, Flag, etc. …

• Stored procedure names ought to reflect the name of the primary data source, the action the procedure accomplishes, and the audience that uses the procedure.

• Picking a table name can be a black art, especially when the procedure spans multiple tables. Generally, the best table name is the one that represents most of the data or the primary join table. Murphy's Law as applied to procedure names: The longest, most inconvenient procedure name is usually the "right" one.

• The audience might typically be one of the following:
• Import
• Export
• Custom
• Operations
• Report
• System (not directly available to the front end)
• Avoid abbreviations where possible though acronyms that have meaning to the business are acceptable.

Tables

• Use the following outline for creating tables:

create table {database name}.{table owner}.{table name}

([{table name}_Id {data type} [identity(begin, step),]]

{column name} {data type} {null | not null},

{column name} {data type} {null | not null}

constraint dft_{table name}__{column name}

default ({default value}),

{column name} {data type} {null | not null}[,

constraint pk{u | n}_{table name}__{pkey column}

Primary key {clustered | non-clustered} ({pkey column})][,

constraint fk_{this table}__{column [list]}__to__{other table}__{column [list]}

foreign key {column [list]}

references {other table}({column [list]})][,

constraint unq_{table name}__{column [list]}

unique key ({column [list]}][,

constraint chk_{table name}__{column [list]}

check ({check expression}),])

Example:

create table School.dbo.TB_COURSE

(Course_Id int identity(1, 1) not null,

Educator_Id int not null,

School_Id int not null,

Course_Name varchar(50) not null,

Start_Date datetime

Constraint dft_TB_COURSE__Start_Date

default (getdate()),

End_Date datetime null,
constraint pkc_TB_COURSE__Course_Id

primary key clustered (Course_Id),

constraint

fk_TB_COURSE__Educator_Id__to__TB_EDUCATOR__User_Id

foreign key (Educator_Id)

references TB_EDUCATOR(User_Id),

constraint

fk_TB_COURSE__Course_Id__to__TB_SCHOOL__School_Id

foreign key (Course_Id)

references TB_SCHOOL(School_Id),

constraint chk_TB_COURSE__Start_Date__End_Date

check (Start_Date <= End_Date))

• The preferred table prefix is TB_ however it is desirable to use an alternate prefix to provide clarity in staging environments. This will help differentiate a staging container from a permanent storage containter.

TSYS_REC_30, EDS_REC_40, PVS_ACCOUNT, etc.

• Object names should include underscores between words.
• Name all tables in the singular form.

TB_CALLING_CARD not TB_CALLING_CARDS

• If a column references an Id in another table, use the full table name. For example, use Title_Id in table TB_AUTHOR to reference column Id or Title_Id in table TB_TITLE.

• Explicitly name constraints. A table or column constraint name will include the table name(s) that it references and the columns affected by the constraint. Separate each table and column in a constraint name with two underscores (__) to differentiate from the single underscore (_) that may be within the table or column’s name.

• A foreign key name will identify both tables participating in a foreign key, the column(s) involved in the relationship, and the direction. The foreign key table (table where constraint is attached) appears first.

fk_TB_COURSE__Educator_Id__to__TB_USER__Id

• Default constraints must be defined at the column level

• Define all Constraints other than defaults at the table level. Constraints that apply to more than one column must be defined and the table level. The standard advocates this method to avoid supporting two methods in the standard. This will provide a more consistent development and conversion interface.

• Avoid rules, database level defaults that must be bound, or user defined data types. While these are legitimate database constructs, opt for constraints and column defaults to hold the database consistent for development and conversion coding.

• Never use a SQL Server reserved word as an identifier name. (Refer to SQL Server Books on Line for a complete listing).
• Unique constraints are preferred over unique indexes for performance reasons, though if a table’s data is accessed by the non primary key unique column(s) a unique index may be appropriate.

• Altering Tables

• alter table should be used in scripts that will be used for upgrading existing table, not when creating tables.

• Use the following outline to drop an existing column or table constraint

If (objectProperty(object_id('{constraint name}'),

'IsConstraint') is not null)

alter table {table name}

drop constraint {constraint name}

• Use the following outline for adding or changing an existing column or table constraint. (Just drop it first if your changing it.)

If (objectProperty(object_id('{constraint name}'),

'IsConstraint') is null)

alter table {fully qualified table name}

add constraint {constraint name}

default {constraint value}

[for {column name}]

• Use the following outline to drop an existing column.

If (ColumnProperty(object_id('{table name}')

,{column name},

'AllowsNull') is not null)

alter table {fully qualified table name}

drop {column name}

• Use the following outline for adding a column.

If (ColumnProperty(object_id('{table name}')

,{column name},

'AllowsNull') is null)

alter table {fully qualified table name}

add {column name} {data type} {null | not null}

[constraint {default name}

default ({default value})]

• Use the following outline for altering a default on an existing column. (Note that It may be necessary to remove foreign keys before altering a column and in a few cases it may be necessary to duplicate the table with the desired new structure and move the data to the new table. This latter operation can be scripted using the EM GUI in a unit test environment rather than coding the entire operation by hand.)

If (ColumnProperty(object_id('{table name}')

,{column name},

'AllowsNull') is not null)

alter table {fully qualified table name} [with nocheck]

alter column {column name} {new compatible data type}

• Alter table examples:

If (columnProperty(object_id('{table name}’),

{column name},

'AllowsNull') is not null)

alter table Plans.dbo.TB_DESIGNS

drop column Color_Id

or