SQL Server Naming Conventions and Standards

Shailendra Chauhan  Print   5 min read  
24 Nov 2012
15 Apr 2019
Beginner
159K

In programming, we have many naming conventions like camelCase, PascalCase, under_scores, etc. But each and every organization has its own naming conventions. In this article, I would like to share some common and useful naming conventions and standards that you should use while programming with SQL Server.

Table

Tables are used to store data in the database. The naming conventions for a table may have a "tbl" prefix, followed by the table name. Moreover, TableName should be plural. The syntax should be "tbl<TableName>".

Examples are below:

tblEmployees
tblOrders
tblProducts 

Primary Key Constraint

Primary key is a field or a set of fields in the database table that uniquely identify records in the database table. A table can have only one primary key. The naming conventions for a primary key constraint should have a "PK_" prefix, followed by the table name. The syntax should be "PK_<TableName>".

Examples are below:

PK_Employees
PK_Orders
PK_Products 

Foreign Key Constraint

Foreign key is a field in the database table that is a primary key in other tables. The naming conventions for a foreign key constraint should have an "FK_" prefix, followed by the target table name, followed by the source table name. The syntax should be "FK_<TargetTable>_<SourceTable>".

Examples are below:

FK_Orders_Employees
FK_Items_Products 

Unique Key Constraint

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It is like Primary key but it can accept only one null value. The naming conventions for unique key constraints should have a "UQ_" prefix, followed by the table name, followed by the column name. The syntax for a unique constraint should be "UQ_<TableName>_<ColumnName(s)>".

Examples are below:

UQ_Employees_EmailID
UQ_Items_Code 

Default Constraint

Default Constraints insert a default value into the column when no value is provided for that column. The syntax for a unique constraint should be "DF_<TableName>_<ColumnName>".

Examples are below:

 DF_Employees_IsActive
DF_Employees_CreateDate

Check Constraint

Check Constraints defines a business rule on a column in the database table that each row of the table must follow this rule. The syntax for a unique constraint should be "CHK_<TableName>_<ColumnName>".

Examples are below:

 CHK_Employees_Salary
CHK_Employees_DOB

User Defined Stored Procedures

Stored Procedures are executed on the server side and perform a set of actions, before returning the results to the client side. This allows a set of actions to be executed with minimum time and also reduce the network traffic. While creating user-defined stored procedures we should avoid the prefix "sp_" with the name of the procedure. Since "sp_" prefix is already fixed for system-defined stored procedures. The naming conventions for user-defined stored procedure may have a "usp_" prefix, followed by the action and objects of the procedure. The syntax for a user-defined stored procedure should be "usp_<Action>_<Object>".

Examples are below:

usp_Insert_Employees
usp_View_EmployeeOrders 

User Defined Functions

Functions are a set of SQL statements that accept only input parameters, perform actions and return the result.The function can return an only single value or a table. The naming conventions for user-defined functions may have an "fn_" prefix, followed by its action. The syntax should be "fn_<Action>".

Examples are below:

fn_CalulateTax
fn_CalculateAge 

Views

Views are like a virtual table that can be made over one or more database tables. Generally, we put those columns in view that we need to retrieve/query again and again. The naming conventions for a view should have a "vw_" prefix, followed by the namespace, results. The syntax should be "vw_<Result>".

Examples are below:

vw_EmpOrderDetails
vw_SalesProductDetails 

Triggers

Triggers are a database object. Basically, these are a special type of stored procedure that is automatically fired/executed when a DDL or DML command statement related to the trigger is executed. The naming conventions for a trigger should have a "trg_" prefix, followed by the action, and the table name. The syntax should be "trg_<Action>_<TableName>".

Examples are below:

trg_Ins_Employee
trg_Upd_Employee
trg_Del_Employee 

Indexes

Indexes are database objects that help the SQL Server query engine to find the desired data. Indexes may be attached to tables or views. The naming conventions for an index should have an "IX_" prefix, followed by the table name and columns. The syntax should be "IX_<TableName>_<Column(s)>”.

Examples are below:

IX_Employee_Name
IX_Employee_NameMobileNo 

Read More Articles Related to SQL Server

What do you think?

I hope you will enjoy these tips while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Take our free skill tests to evaluate your skill!

In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.

Learn to Crack Your Technical Interview

+
+
Accept cookies and close this message