SQL Server Naming Conventions

The naming conventions documented here represent a meeting point between a best guess at consensus and the author's personal preference.

General Rules

Avoid names that require quotes or brackets. Generally this means names with white spaces. Naming a table Order Detail, instead of OrderDetail can lead aggravated hours debugging. Additionally, when using brackets in an SQL statement can lead to more heartburn.

Here are some examples:

CREATE TABLE dbo.[OrderDetail ] ...
CREATE TABLE dbo.[Order Detail] ...

The DDL above creates two tables, one with a trailing space and one with space in the middle. So then when you query SELECT * FROM dbo.OrderDetail one will get errors. Without the brackets, the second table will cause an error because Order is a key word

Avoid using reserved keywords for object names. One can use reserved keywords as object names. Because you can doesn't mean you should. This can cause performance issues or errors.

Avoid abbreviations. SQL Server allows 128 characters for column names. So don't be afraid of verbose names. Abbreviations, no matter how obvious, can cause confusion for a new person on the development team. Name an object that is plane enough for someone completely new to the code base to understand.

Tables

Tables names should be singular nouns. Use a singular noun that plainly describes something what is being stored.

Columns

Acolumn name should describe an attribute of the table. Use a singular noun that plainly describes something about the table.

A column name should not include the name of the table. Using the table name in the column name is redundant. For example use FirstName instead of CustomerFirstName in dbo.Customer.

Surrogate keys should be named Id. Use of the name Id indicates a surrogate key. For a natural key, use the normal naming convention of describing an attribute of the table.

Foreign keys should be a concatenation of the reference table and primary key. A table that has a foreign key constraint to the dbo.Customer table should be named CustomerId.

Views and User Defined Table Valued Functions

Views and table valued functions should be plural nouns. Use a plural noun that plainly describes something what is being stored. Typically the plural form of an existing table. For example a view name dbo.Customers,which is derived from a table named dbo.Customer.

Stored procedures and User Defined Scalar Functions

Use a singular verb noun combination to name stored procedures and scalar functions. Borrowing from PowerShell Design Guidelines, stored procedures should combine a verb and a specific noun. For example, use the name dbo.InsertCustomer for a stored procedure that inserts a row into the dbo.Customer table. This convention will make it easier to discover and infer what the stored procedure is meant to do.

Constraints

Primary key names should be a combination of PK plus the table name. For example PK_Customer.

Foreign key name should be a combination of FK plus the table name and referenced table name. For example, a foreign key constraint on dbo.OrderDetail that references dbo.Order should be named FK_OrderDetail_Order.

Default constraints should be a combination of DF plus that table name and column. For example, and default constraint on a column named DateCreated on the dbo.Customer table should be named DF_Customer_DateCreated.

Indexes

Indexes should be a combination of a prefix plus, table name and column name. Index with multiple will to be judged on a case by case bases. Use one of the following prefixes:

  • PK for primary keys
  • CLIX for clustered unique indexes
  • IX for non clustered index
  • COIX for covering indexes

Schemas

SQL Server schema objects useful as a namespace, access control mechanism or application interface. reference.

Schema names should be a lower case noun that logically groups objects. For example, an application that manages the business activities of an academic institution by have schemas such as student, faculty, academy, etc.


Posted 09 April 2018