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.

Skip to main content