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.