SQL 2005 - Coding Standards - Database Object Naming Rules

Database Object Naming Rules

Summary:

Schemas

• Use lowercase for schema names.
• Always alias database objects using the schema name, even if this is the default [dbo] schema
• This applies to both CREATE statements and when referencing objects in FROM, INSERT or UPDATE statements etc.

Table Names

• Pascal Case
• Alpha-numeric
• Avoid underscore
• No Prefix
• Use the Singular Form eg: User, not Users

Linking Table Names

• Linking Tables should be the name of the two tables it is joining, suffixed with Link. Eg a joining table on User and Group would be UserGroupLink

Column Names

• Pascal Case
• Alpha-numeric
• Avoid underscore

• No Prefix
• Format: <TableName(for PK only)><Qualifier><Name>

use the following components in the order below;

• • Table Name: Primary keys only; Tables names are used to prefix all columns in dotted format, so this is not necessarily. The exception is the primary key since this is used in foreign keys.
  • Qualifier: Optional; Description, to clarify the meaning of the field. For example, if a product has two images, this would clarify the field, eg. FrontImage and RearImage
  • Name: Required; This is a database independent “datatype” descriptor which is used to classify the type of data. Below is a common list of standard classifiers. The exception to this is a Boolean. This should be Prefixed with “Is” as this more positively represents the meaning o the value. Flag suffix is considered optional “Flag” or Eg. IsEnabled or IsEnabledFlag

Stored Procedure Names

· Use PascalCase

• Naming Format: use the following components in the order below;
  • Object: Required; usually the table or combinations of tables it is affecting, followed by underscore.
  • Action: Required; eg Save, Load, Get, Set, SetSingle, Search, Delete
  • Qualifier: Optional; additional descriptive words which help to clarify the specific meaning of the stored procedure
  • Return Type: Optional; Indicates the type of data return
• Example Stored Procedure Names:
  • AuthorSave
  • AuthorLoad
  • AuthorLoadByAuthorID
  • AuthorLoadByName
• Do not:
  • Use special characters.
  • Use stored procedure group numbers (e.g. myProc;1).
  • prefix names with “sp_” as those are reserved for procedures shipped by SQL Server.

User Defined Functions (UDF) Names

· Use PascalCase

• Naming Format: use the following components in the order below;
  • Prefix: Required; “FN_”
  • Object: Required; usually the table or combinations of tables it is affecting, followed by underscore.
  • Action: Required; eg Get, Set, SetSingle, Search, Delete
  • Qualifier: Optional; additional descriptive words which help to clarify the specific meaning of the stored procedure
  • Return Type: Optional; Indicates the type of data return
• Example Function Names:
  • FN_AuthorGetID
• Often stored procedures will replicate (wrap) a user defined function. In this case the names should be identical with the exception of the additional prefix on a UDF.
• Note, udfs cannot have any “effects” so cannot modify data.

Parameters - Stored Procedure/UDFs

• Use PascalCase
• Eg: @PageID

Variables - Stored Procedure/UDFs

• Use camelCase
• Eg: @pageID

Cursor Names

· Use PascalCase, except for prefix

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix with “CURSOR_”
  • Object: Required; usually the table being iterated over.
• Note: Avoid the use of cursors where possible. Instead use a while loop

Updatable View Names

For Views which are updatable, act as if they are a table.

This holds true for Updatable Partitioned Views.

· Use PascalCase, except for prefix

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix with “VW_”
  • Object: Required; usually related to the table(s) affected by the view
  • Qualifier: Optional; additional descriptive words which help to clarify the purpose of the view.

Non Updatable View Names

For Views which provide a view on the data which makes them read only.

· Use PascalCase, except for prefix

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix with “VW_”
  • Object: Required; usually the concatenation of tables in the view
  • Qualifier: Optional; additional descriptive words which help to clarify the purpose of the view.

Trigger Names

· Use PascalCase, except for prefix

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix with “TRG”
  • Type: Required; depending on type of trigger, after or instead of. prefix with “A_” or “I_”
  • Object: Required; usually the table being iterated over.
  • Actions covered: Required; composite key of actions, “Upd”, “Ins”, “Del”

· Example Trigger Names:

o TRGA_CustomerInsUpdDe

o TRGA_ProductDel

o TRGI_AuthorUpd

Index Names

Index names are unique within a table so it isn’t necessary to include the tablename in the index. When looking at execution plans it is helpful to have a hint about the columns being indexed

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix with “IX”
  • Clustered: Required; if Clustered Index include “C”
  • Unique: Required; if Unique Index include “U”
  • Column Names: Required; Include the list of columns indexed, using underscores between the column names. For an index that covers all columns in the table, use the word All.

· Example Index Names:

o IXCU_AuthorID (clustered unique)

o IXU_AuthorID (unique)

o IX_AuthorID_AuthorName (composite index)

o IXC_AuthorID (clustered not unique)

Primary Key Names

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix primary key with “PK_”
  • TableName: Required; Table name of table being keyed
• Examples:

o PK_Customer

Foreign Key Names

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix foreign key with “FK_”
  • Reference Table Name(s): Required; Table name of table in the join, for which a unique index is on column(s) being linked. Where both have a unique index, such as linking key, order is optional
  • Foreign Table Name(s): Required; Table name of table in the join, for there is not a unique index on the column(s) being linked.
• Example foreign key names:
  • FK_Country_Customer
  • FK_Customer_Sales

Default Value Constraint Names

• Naming Format: use the following components in the order below;
  • Prefix: Required; prefix default value constraint with “DF_”
  • TableName: Required; Table name
  • ColumnName: Required; Column name
• Example foreign key names:
  • DF_Author_Gender

Check Constraint Names

• Naming Format: use the following components in the order below;

  • Prefix: Required; prefix check constraint with “CK_”

  • TableName: Required; Table name

  • Integer: Required; Where the integer id is used to distinguish the check constraint from other check constraints on the same table.

  •  

• Example foreign key names:
  • CK_Author1

Abbreviation Standards

Avoid abbreviations, unless absolutely necessary, due to length restrictions

Database Collation

• For new databases use: Latin1_General_CI_AS
• For migrated databases, keep with the same collation as specified in the source database – often this will be: SQL_Latin1_General_Cp1_CI_AS as this is the default for a database migrated from SQL 7.0 to SQL2000
• Ensure all columns use this option. They will if they are created in the database using, the correct collation.

Comments

• Anonymous
  June 07, 2009
  PingBack from http://greenteafatburner.info/story.php?id=174