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.
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 names should be singular nouns. Use a singular noun that plainly describes something what is being stored.
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
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
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
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.
Primary key names should be a combination of PK plus the table name. For example
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
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
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
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
Posted 09 April 2018