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.