DataTamer: SQL Server Services

Naming Conventions

Naming conventions are used to streamline development when multiple developers are working on a single system, as well as to simplify ongoing maintenance during the Development Lifecycle. There are many different naming conventions for SQL Server and no single convention is necessarily right or wrong. However, a single consistent naming convention should be followed within each database to reduce confusion and to enhance usability. Ideally, follow a single naming convention throughout all SQL Servers in an organization.

General Notes

  • Only use letters, numbers and underscores in the names of database objects. Specifically, never use a space as these can create many problems in other applications.
  • Save the lowly shift key! Start with lower case.
  • Use singular names rather than plural. This is often debated rather hotly on the SQL forums - singular naming simply saves a character when coding.
  • Use mixed case when combining names rather than underscores to indicate word breaks (in most cases). Use "customerAddress" instead of "customer_address".
  • Reserved keyword conflict can be avoiding for any database object by using the square bracket text qualifiers []. The SQL Server Books Online help file also contains a list of reserved words under the title "Reserved Keywords (Transact-SQL)".
  • Avoid extremely long names but don't oversimplify past the point of readability. Too many acronyms makes it difficult for new developers to follow the design.

Databases

  • Single database applications may use any simplified name. Multiple database applications should use a prefix followed by the database category.
  • Samples:
    • "finance" for Financial Operations
    • "operations" for Operations (okay, perhaps this one was obvious)
    • Prefix of "HR" for Human Resources
      • "HRData" for the primary database
      • "HRImport" for data import holding tables and procedures
      • "HRExport" for data export holding tables and procedures

Backup Files

  • Prefix all backups with the database name. Use an underscore and add the date and time of the backup.
  • Samples:
    • Full backup: dbname_200601011800.bak
    • Differential backup: dbname_200601011800.dif
    • Transaction Log: dbname_200601011800.trn

Users and Logins

  • Match all database user names to the mapped login. This simplifies security audits.
  • No user accounts should be shared among logins. Use database roles to achieve continuity instead.

Tables

  • Tables do not require a prefix (contrary to certain sample databases).
  • Complete the name with the primary entity stored in the table in a singular form.
  • Name tables that rely on other tables in sequence using the primary table name as a starting point.
  • Name many-to-many tables by the nature of the join. As an example, join users to roles in a table named "profile" and join roles to abilities in a table named "permission".
  • Holding tables for temporary data should be prefixed with "temp". Assuming you are daring enough to use them: NOTHING is as permanent as a temporary table. Never, ever link a stored procedure to a table with the "temp" prefix. It is a virtual guarantee that this will lead to a "temp" table being used in production. Sooner or later someone will delete this "temp" table and break your production system. Especially if I am your DBA.
  • Samples:
    • company, customer, product, invoice
    • companyAddress, customerAddress, invoiceDetail
    • profile, permission
    • tempCustomerBackup
  • Comments:
    • Avoiding keywords is simple by using the SQL Server text qualifier square brackets. For example, "user" is a common desired table name that is also a keyword, but [user] is not.

Columns

  • Name columns according to the information they contain. Primary keys should use the table name plus the suffix "ID".
  • Samples:
    • customerID, customerName, customerNumber, Address, City, Country
    • firstName, lastName, phone
    • createOn, createBy, editOn, editBy, deleteOn, deleteBy
  • Comments:
    • Be consistent with column names between tables. Don’t refer to a primary key as "customerID" in one table and as "custID" in another.
    • Don’t prefix columns with their data type. This is unnecessary and makes for extra typing.

Indexes

  • Use the table name as a prefix plus the first indexed column name. If creating more than one index starting with this column, use as many as necessary to uniquely identify the index. Then ask yourself why your indexes overlap.
  • Always explicitly name your indexes rather than allowing SQL Server to generate names. This makes indexes easier to trace and to understand just by looking at the name.
  • Samples:
    • customer_customerID, customer_customerName_address, customer_customerName_customerNumber

Constraints

  • Use the table name as a prefix plus the constrained column name.
  • Always explicitly name your constraints rather than allowing SQL Server to generate names. This makes constraints easier to trace and to understand just by looking at the name.
  • Samples:
    • Primary Key: customer_customerID
    • Foreign Key: invoice_customerID
    • Unique: invoice_invoiceNumber

Views

  • Views do not require a prefix.
  • Complete the name with the primary entity displayed by the view in a singular form.
  • For views that merge entities, use a combined name starting with the primary table.
  • Samples:
    • customerDetail, customerAddress, invoiceDetail, customerInvoiceDetail
  • Comments:
    • Although consistency in naming between tables and views allows them to be used interchangeably in accordance with ANSI standards, I prefer a clear difference provided by the merged names. This is because I do not allow direct access to either tables or views to the users of my systems. All access is provided through stored procedures that feed various reports.

Stored Procedures

  • Prefix all stored procedures with the base table name (most procedures act on a specific table or tables). Complete the name with an underscore and the primary job performed. This will group all procedures for a given table in one location alphabetically.
  • Use the prefix "report_" for stored procedures that directly support report generation.
  • Samples:
    • customer_list, customer_search, customer_create, customer_read, customer_update, customer_delete, customer_purge
    • report_customersByCountry, report_customersBySales
  • Comments:
    • Never prefix a stored procedure with "sp_". This will cause a performance hit against your system as SQL Server always searches the Master database for these stored procedures first.

User-Defined Functions

  • Prefix all user-defined functions with "f". Add a shortened description of functionality.
  • Samples:
    • fSplit, fMixedCase

Triggers

  • Prefix all triggers with "tr". Add the table name and trigger type.
  • Samples:
    • trCustomerInsert, trCustomerUpdate

Behind The Scenes

I was once faced with the task of "cleaning up" a database with more than 1200 tables and 2000 stored procedures. 80% of these tables and procedures were not in use and needed to be removed. A standard naming convention would have made the cleanup work much faster and would have allowed new developers to learn the system much faster as well.

In addition, a standard naming convention would have allowed for directed text searches to trace specific stored procedures and tables. This would have allowed consolidation of some redundant procedures without having to resort to a "change it and see what breaks" testing methodology.