Naming conventions while coding in SQL Server

Purpose of this post is to standardize naming convention for SQL Server database objects so everyone can easily sense the type and purpose of the Object. A set of rules and guidelines are provided to follow while naming Database Objects. 

Name is a sequence of characters that identifies a database object. 

Naming Convention Rules for all database objects: 

• Name object meaningful, simple and shorter; avoid abbreviations where possible and use whole words   

1. 1. Name object with mixed case and initial capitalized  
   2. Avoid numbers and special characters 
   3. Avoid SQL Server reserved keywords for name and alias  
   4. Avoid spaces in names even allowed in SQL Server 
   5. Avoid underscores in names as much as you can; except where defined in the document   
      

1.  

   User-defined Tables: Use proper case for each word in name. Group related table names like Users, UserRoles, UserAddresses 

Examples:  

• PSSRules 
• PSSAssignedRules 
  

2.  

   Table Columns: If a column is referencing another table for foreign key use <table name>ID 

Examples: 

• UserID 
• UserAddressID 

 

3. User-defined Views:   vw_<description or nature of the view > 

Examples:  

• vw_Users 
• vw_UsersCallHistory 

 

4. User-defined Functions:   fn_<description or nature of the fucntion > 

Examples: 

• fn_Splittoken 
• fn_GetFormatedDate 

 

5. User-defined Stored Procedures:    <GroupName>_<Action< description or nature of procedure > 

Where action is: Get, Delete, Update, Write, Archive, Insert ... i.e.   

Examples: 

• PSS_GetAllRules 
• PSS_DeletedRuleReason 

Note: Do NOT use prefix “sp_” as SQL Server first consider it as system stored procedure 

 

6. Primary Key Constraints:   PK_<TableName>_<ColumnName(s)> 

Examples: 

• PK_Users_UsersID 
• PK_UserGroup_GroupID

 

7. Foreign Key Constraints:   FK_<ForeignTableName>_<ColumnName(s)> 

Examples: 

• FK_UserRules_RuleID 

 

8. Unique Key Constraints:   uk_<column names separated with underscore (_) > 

Examples: 

• UK_UserID_AccNo 
• UK_FirstName_LastName 

 

9. Check Constraints:   chk_<description or nature of the constraint> 

Examples: 

• chk_SSN 
• chk_DuplicateAccount 

 

10. Default Constraints:   DF_<column name> 

Examples: 

• DF_CellNo 

 

11. User-defined Table Types:  udtt_<description or nature of the table > 

Examples: 

• udtt_Users 
• udtt_MessageSetting 

 

12. Triggers:   trg_TableName_<Trigger type (Instead Of/After)>_< Operation(Insert/Update/Delete) > 

Examples:  

• trg_Users_After_Update 
• trg_UserDocuments_After_Delete 
  

Note: Use of triggers is always discouraged  

 

13. Indexes:   IX_<column names separated with underscore (_)>  

Examples: 

• IX_UserID_active 

• IX_UserAccountNumber  

   
  

14. Object-level Backup: <DatabaseName>_<TableName>_BCK<Date> 

• Make a centralized database to store backups for recovery 

• This centralized setup will be easy to clean and maintain 

Examples: 

• UsesrData_AccountHeader_BCK011619 

• UsersData_Documents_BCK011619 

 

Object Name	Rule	Examples
Tables	Use proper case for each word in name.   Group related table names	PSSRules  PSSActiveRules
Table Columns	If a column is referencing another table for foreign key use   <table name>ID	UserID  UserAddressID
Views	vw_<description or nature of the view >	vw_Users  vw_UsersCallHistory
Functions	fn_<description or nature of the fucntion >	fn_SplitToken  fn_GetFormatedDate
Stored Procedures	<GroupName>_<Action< description or nature of procedure >   Where action is: Get, Delete, Update, Write, Archive, Insert ... i.e.    Note: Do NOT use prefix “sp_” as SQL Server first consider it as system stored procedure	PSS_GetAllRules  PSS_DeleteRuleReason
Primary Key Constraints	PK_<TableName>_<ColumnName(s)>	PK_Users_UsersID  PK_UserGroup_GroupID
Foreign Key Constraints	FK_<ForeignTableName>_<ColumnName(s)>	FK_UserRules_RuleID
Unique Key Constraints	UK_<column names separated with underscore (_) >	UK_UserID_AccNo  UK_FirstName_LastName
Check Constraints	chk_<description or nature of the constraint>	chk_SSN  chk_DuplicateAccount
Default Constraints	DF_<column name>	DF_CellNo
User-defined Table  Types	udtt_<description or nature of the table >	udtt_Users    udtt_MessageSetting
Triggers	trg_TableName_<Trigger type (Instead Of/After)>_< Operation(Insert/Update/Delete) >    Note: Use of triggers is always discouraged	trg_Users_After_Update  trg_UserDocuments_After_Delete
Indexes	IX_<column names separated with underscore (_)>	IX_UserID_active  IX_UserAccountNumber
Backup Object for recovery	Should be stored in a centralized database  <DatabaseName>_<TableName>_BCK<Date>	UsesrData_AccountHeader_BCK011619  UsersData_Documents_BCK011619