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

Leave a Reply

Your email address will not be published. Required fields are marked *