Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
SQL Server Naming Conventions and Standards

SQL Server Naming Conventions and Standards

14 Mar 2024
Beginner
239K Views
12 min read

SQL Server Naming Conventions and Standards: An Overview

In programming, we have many naming conventions like camelCase, PascalCase, under_scores, etc. But every organization has its 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


tblEmployees
tblOrders
tblProducts 

Primary Key Constraint

A primary key is a field or a set of fields in the database table that uniquely identifies 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


PK_Employees
PK_Orders
PK_Products 

Foreign Key Constraint

A 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


FK_Orders_Employees
FK_Items_Products 

Read More: Difference between Primary Key and Foreign Key

Unique Key Constraint

A unique key is a set of one or more fields/columns of a table that uniquely identifies a record in a database table. It is like a 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


UQ_Employees_EmailID
UQ_Items_Code 

Read More: Difference between Primary Key and Unique Key

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


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


 CHK_Employees_Salary
CHK_Employees_DOB

Read More: SQL Integrity Constraints or Constraints

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 reduces 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 procedures 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


usp_Insert_Employees
usp_View_EmployeeOrders 

User Defined Functions

Functions in SQL Server are a set of SQL statements that accept only input parameters, perform actions, and return the result. The function can return only a 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


fn_CalulateTax
fn_CalculateAge 

Views

Views in SQL Server are like virtual tables 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


vw_EmpOrderDetails
vw_SalesProductDetails 

Triggers

Triggers in SQL Server are database objects. 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


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


IX_Employee_Name
IX_Employee_NameMobileNo 

Guidelines for Naming Conventions

1. Use CamelCase or Underscores

Choose a consistent casing convention like CamelCase (e.g. EmpNo) or underscores (Emp_No) for naming database objects.

2. Use Prefixes or Suffixes for Constraints and Indexes

Prefix or suffix constraint and index names with descriptive identifiers.

Example

use PK_ for primary key constraints and FK_ for foreign key constraints.

3. Avoiding reserved keywords

  • Avoid using reserved words or keywords as names for database objects.
  • If a reserved word must be used, consider prefixing or suffixing it with another word to differentiate it (e.g., student_table).

4. Clarity and Readability

  • Use clear and concise names that reflect the data or functionality represented by the object.
  • Do not use special characters or spaces in object names.
  • Use only alphanumeric characters and underscores for compatibility and readability.

5. Consistency across objects

  • Stick to a consistent naming style throughout the database schema.
  • Pay attention that all developers working on the database follow the same conventions.

6. Abbreviations and acronyms

  • Choose column names that represent the data they hold.
  • Don't go for abbreviations or overly long names.
  • Using abbreviations for object names is inadvisable, but so is using names that are too long.
  • You must be descriptive but concise in naming columns (e.g., Emp_name, Birth_date).

Read More: SQL Server Interview Questions and Answers

Examples of Naming Conventions

Table naming examples

  • When naming tables, we generally have two options: to use the singular for the table name or to use a plural.
  • If you’re naming entities representing real-world facts, you should use nouns. For example, tables like employee, customer, city, etc. If possible, use a single word that exactly describes what is in the table.
  • If it's required to use more than one word to describe what is in the table, use the words joined by underscore(_). For example, you have two tables employees, and work. We want to add a many-to-many relation telling us that an employee had a certain work. We could use the names employee_has_work, or if we want to be shorter useemployee_work.

Stored Procedure Naming Examples

  • If the store procedure is using only one table, name it p__.
  • p_employee_insert inserts a new row in the table employee; p_employee_delete deletes a row, p_employee_all returns all employees from the table, while p_employee returns only 1 employee.
  • If the procedure uses more than 1 table, use a descriptive name for the procedure.
  • If we want all employees with 5 or more calls, it's good to call this procedure similar to this – p_employee_with_5_or_more_calls

View Naming Examples

  • Use prefixes or suffixes to distinguish views from tables

    Example

    • vw_: Prefix denoting a view (e.g., vw_EmployeeDetails).
    • _view: Suffix denoting a view (e.g., EmployeeDetails_view).
  • Use CamelCase or underscores to separate words within view names

    Example

    EmployeeRoles, Employee_Roles

Function Naming Examples

You must describe what the function does as it's intended for a specific role. It's good if you put f_ at the start of the name of the function.

Example


f_calculate_salary

Best Practices for Naming Standards

  • Names of database objects such as tables, columns, stored procedures, views, etc, can contain alphanumeric characters and must begin with an alphabet or an underscore.
  • Object names can contain numbers, #, $, @, but try to avoid them.
  • Use full English words and avoid abbreviations.
  • Embedded spaces or special characters are not allowed.
  • Avoid quotes while naming a database object.
  • For table names, you can use either singular or plural.
  • Use underscores where you would naturally include a space in the name.
  • Never give a table the same name as one of its columns and vice versa.
  • Always use uppercase for the reserved keywords like SELECT and WHERE.
Summary

The above guidelines are going to help you understand further tutorials on SQL Server if you get thorough with these. You need to first of all understand it and then apply it. For practical and hands-on, consider our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

FAQs

Q1. What is the standard naming convention in SQL Server?

  • Use CamelCase or Underscores
  • Use Prefixes or Suffixes for Constraints and Indexes
  • Avoiding reserved keywords
  • Clarity and Readability
  • Consistency across objects
  • Avoid Abbreviations and Acronyms

Q2. What are the naming conventions for views in SQL Server?

  • Descriptive and Meaningful Names
  • Consistency with Table Names
  • Avoiding Abbreviations
  • Maintaining Consistency

Q3. What is the naming convention for named instances in SQL Server?

  • Use of Alphanumeric Characters
  • Use of Prefixes or Suffixes
  • Avoidance of Reserved Keywords

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this