SQL naming conventions

SQL naming conventions

This article is for standards for the SQL naming convention. I listed all basic which I found useful. May this will benefit you too.

---

Databases and Files

• The database name should accurately reflect the database content and function. Use the Pascal case structure for database names. E.g., Northwind

• Use both upper and lower case letters as appropriate. Do not use spaces or underscores in the name
• File names must match the database name

Tables

• Table names should accurately reflect the table’s content and function
• Use the Pascal case structure for table names, E.g. CustomerOrder
• Do not use spaces or underscores in the name
• Use singular definitions such as CustomerOrder as opposed to CustomerOrders
• All tables must have a primary key identifying each row

Table Columns

• Each column name must be unique within its table
• Use the Pascal case structure for column names. E.g. CustomerName
• Do not use spaces or underscores in the name
• Do not use reserved or key words as object names
• Primary keys for tables should be prefixed table name and ‘Id’. E.g. CustomerOrderId

 #Sample  1:

CREATE TABLE [CustomerOrder]
(
  [CustomerOrderId] int IDENTITY(1,1) NOT NULL,
  [CustomerName] varchar(100) NULL,
  [Address] varchar(100) NULL,
)

Table Indexes

Indexes are named to indicate the table they are attached to and the purpose of the index.

• Primary keys have a prefix of ‘pk_’
• Foreign keys have a prefix of ‘fkz_’ where z is a number that is incrementally assigned
• Clustered indexes have a prefix of ‘idx_’
• All other indexes have a prefix of ‘ndxz_’ where z is incrementally assigned

Only one prefix per index may be appended. The application of the appropriate prefix should follow the following hierarchy: primary key, clustered index, foreign key, other index. E.g., an index that is both a primary key and clustered should have a prefix of ‘pk_’. It is good practice to index columns that are frequently used in a query’s selection criteria.

#Sample 2:

CREATE TABLE [CustomerOrder]
(
 [CustomerOrderId] int IDENTITY(1,1) NOT NULL,
 [CustomerName] varchar(100) NULL,
 [Address] varchar(100) NULL,
 CONSTRAINT pk_CustomerOrderId PRIMARY KEY (CustomerOrderId)
)

Views

• View names follow the same conventions as table names, prefixed with the lowercase literal ‘vw_’. E.g., vw_CustomerOrderItem

Stored Procedures

• System level stored procedures are named using a prefix ‘sp__’ (two underscores) and a description of what the stored procedure does
• All application level and user defined stored procedures are prefixed with the lowercase constant ‘usp_’ with a description of what the stored procedure does. E.g., usp_GetLastCustomerOrder

Triggers

• Triggers are named to indicate the table they are for and the type of trigger. The purpose of the trigger is identified in the prefix to the name.
• All triggers should be prefixed with the lowercase letter ‘t’, a letter(s) designating the type,and the table name. The type should be designated as ‘i’ = insert, ‘u’ = update, ‘d’ = delete.

E.g., ti_Customer, tu_Customer or td_Customer