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.