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:
1. tblEmployees
2. tblOrders
3. 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 constraints should have a
"PK_" prefix, followed by the table name. The syntax should be
"PK_<TableName>".
Examples are below:
1. PK_Employees
2. PK_Orders
3. PK_Products
Foreign Key Constraint
Foreign
key is a field in the database table that is primary key in other
table. The naming conventions for a foreign key constraint should have a
"FK_" prefix, followed by the target table name, followed by the
source table name. The syntax should be
"FK_<TargetTable>_<SourceTable>".
Examples are below:
1. FK_Orders_Employees
2. 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 database table. It is like Primary key but it can accept
only one null value. The naming conventions for a 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:
1. UQ_Employees_EmailID
2. 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:
1. DF_Employees_IsActive
2. 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:
1. CHK_Employees_Salary
2. 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 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:
1. usp_Insert_Employees
2. usp_View_EmployeeOrders
User Defined Functions
Functions
are a set of SQL statements that accepts only input parameters, perform actions
and return the result.Function can return only single value or a table. The
naming conventions for user defined functions may have a "fn_"
prefix, followed by it's action. The syntax should be
"fn_<Action>".
Examples are below:
1. fn_CalulateTax
2. 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:
1. vw_EmpOrderDetails
2. vw_SalesProductDetails
Triggers
Triggers
are database object. Basically these are special type of stored procedure that
are automatically fired/executed when a DDL or DML command statement related
with 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:
1. trg_Ins_Employee
2. trg_Upd_Employee
3. trg_Del_Employee
Indexes
Indexes
are database objects that help the SQL Server query engine to find the desired
data. Indexes may 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:
1. IX_Employee_Name
2. IX_Employee_NameMobileNo
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.
For more information, go to http://www.isbe.state.il.us/ILDS/pdf/SQL_server_standards.pdf
No comments:
Post a Comment