Sql Coding Standards

Online Survey

http://stackoverflow.com/questions/7662/database-table-and-column-naming-conventions

http://www.nyx.net/~bwunder/dbChangeControl/standard.htm

http://selectinsql.blogspot.in/2012/08/sql-server-stored-procedure-coding.html

http://blog.sqlauthority.com/2007/06/04/sql-server-database-coding-standards-and-guidelines-part-1/

http://blog.sqlauthority.com/2007/06/05/sql-server-database-coding-standards-and-guidelines-part-2/

http://www.pinaldave.com/sql-download/SQLServerGuideLines.pdf

http://justinsomnia.org/2003/04/essential-database-naming-conventions-and-style/

Naming

Naming by Pinal Dave

Tables:

Rules: Pascal notation; end with an ‘s’

Examples: Products, Customers

Group related table names1

Stored Procs:

Rules: sp_[_]<table/logical instance>

Examples: spOrders_GetNewOrders, spProducts_UpdateProduct

Indexes:

Rules: IX__

Examples: IX_Products_ProductID

Primary Keys:

Rules: PK_

Examples: PK_Products

Foreign Keys:

Rules: FK__

Example: FK_Products_Orderss

Defaults:

Rules: DF__

Example: DF_Products_Quantity

Columns:

If a column references another table’s column, name it <table name>ID

Example: The Customers table has an ID column The Orders table should have a CustomerID column General Rules:

Do not use spaces in the name of database objects Do not use SQL keywords as the name of database objects In cases where this is necessary, surround the object name with brackets, such as [Year] Do not prefix stored procedures with ‘sp_’2 Prefix table names with the owner name3

Craig Document Version One

Naming

General

Do not use Sql reserved words for object names. If for some reason a Sql reserved word must be used enclose in square brackets. For example.

[user]

Tables Names

Pascal notation. Singular form.

Examples

  • AgentDispo
  • Dispo
  • TZState
  • JobDispo

This table name convetion is seen in current usage.

Stored Procedure Names

            
            proc_camelCaseNameOfProcedure

For example

  • proc_login
  • proc_groupScore

Notes:

The prefix sp_ is reserved for system stored procedures that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. Time spent locating the stored procedure can be saved by avoiding the “sp” prefix.

Index Names

Rule:


            IX_TableName_(columns separated by _)

Note that if the column name starts with the table name, omit the table name in the index name.

Examples:

  • IX_AgentGroup_Agent_ID_Group_ID
  • IX_Dispo_Code

Primary Key Column names:

Each table must have a primary key. In most cases it should be an IDENTITY column named 


            TableName_ID.

For instance.

  • Quiz_ID in table Quiz.
  • Dispo_ID in table Dispo.
  • Group_ID in table Group.

Foreign Key Constraint Names:

Rules:


            FK_(TableName1)_(TableName2)

Examples:

  • FK_AgentGroup_Agent
  • FK_AgentGroup_Group

Defaults:

Rules: 


            DF_(ColumnName)

Example:

DF_AgentGroup_Visible

Note our convention is that column names are prefixed with the table name.

Columns:

Column names will have the table name as a prefix. Use camel case notation after the table name prefix.

For example

  • column Agent_lastLogin in table Agent
  • column Dispo_name in table Dispo.
  • column AgentGroup_visible in table AgentGroup

If a column references another table’s column, name it 


            (table name)_(primary key column)

For example, the Agent table has a primary key Agent_ID, the AgentGroup table would have a AgentGroup_Agent_ID column.

  • AgentGroup_Agent_ID

Standard Abbreviations

It will be useful to have a list of GCS standard prefixes. The First one seen was TZ.

  • TZ - timezone

Query Formatting Examples

TBD