Share via

SQL 2005 - Coding Standards - Database Object Naming Rules

Database Object Naming Rules

Summary:

Casing

Prefix

Suffix

Alpha Numeric Characters

Notes

Tables

Pascal

x

Use singular form: Eg User, not Users

Linking Tables

Pascal

Link

x

Formed from the Tables they are linking, eg: A Table joining User and Group would be UserGroupLink

Table Columns

Pascal

x

Primary Key

Pascal

PK_

x

Clustered Index

Pascal

IXC_

x

Unique Clustered Index

Pascal

IXCU_

x

Unique Index

Pascal

IXU_

x

Index

Pascal

IX_

x

XML Index

Pascal

XML_IX_

x

XML Columns

Pascal

x

Use .net Pascal casing, no underscores

Constraints

Pascal

CK_

x

Default Value

Pascal

DF_

x

Foreign Keys

Pascal

FK_

x

Views

Pascal

VW_

x

Functions

Pascal

FN_

x

Stored Procedures

Pascal

none

x

Triggers (after)

Pascal

TRGA_

x

Triggers (instead)

Pascal

TRGI_

x

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

Classifier

Description

Suggested SQL Data Type

Address

Street or mailing address data

nvarchar

Age

Chronological age in years

int

Average

Average; consider a computed column

numeric

Amount

Currency amount

money

Code

Non Database Identifier

Count

Data

A field containing extensible data

xml

Date

Calendar date

smalldatetime

Datetime

Date including time

datetime

Day

Day of month (1 - 31)

tinyint

Description

Brief narrative description

nvarchar(MAX)

Duration

Length of time, eg minutes

int

ID

Unique identifier for something

int

Image

A graphic image, such as a bitmap

varbinary(MAX)

Flag

Not Required: Flag indicates a boolean indicator, where the Qualifier verb does not make it clear it is a verb. Examples of a Qualifier are: Is, Has, Uses. Eg IsEnabled

bit

Month

Month of year

Name

Formal name

nvarchar

Number

Percent

Number expressed as a percent

Quantity

A number of things

any numerical

Rate

Number expressed as a rate

any numerical

Ratio

A proportion, or expression of relationship in quantity, size, amount, etc. between two things

any numerical

Sequence

A numeric order field

int

Text

Freeform textual information

nvarchar(MAX)

Time

Time of day

smalldatetime

Title

Formal name of something

nvarchar

Version

Timestamp

timestamp

Weight

Weight measurement

any numerical

XML

A field containing xml data

xml

Year

Calendar year or julian year number

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