In this article, some most common guidance in naming conventions will be given and shown how ApexSQL Refactor, SQL formatting Visual Studio and SSMS add-in with nearly 200 formatting options, can help in achieving capitalization consistency among team members.
Database identifier naming rules
Database object names are referred as identifiers, and are created when a database object is defined. For example, the following statement creates a table with the identifier MyTable and a column with the identifier KeyColumn:
CREATE TABLE MyTable (KeyColumn INT PRIMARY KEY)
Many developers use singular names when naming a table, e.g. Person, Employee, Product, List.
This is also used as a standard in Adventure Works databases and many guidelines. The reason for this suggestion is avoiding errors due to pluralization in the database development process.
However, if you choose to follow the ISO 11179 naming convention standard use plural names, e.g. Persons, Employees, Products, Lists.
Stored procedures names must comply with the rules for identifiers. Also, the prefix in the name of the stored procedure should not be sp_. sp_ is a reserved prefix for system stored procedures, and naming a stored procedure with a sp_ prefix may lead to a conflict with system stored procedures that may be added in the future. SQL Server will look for a stored procedure that begins with a sp_ prefix in the master database first, and if any stored procedure created by the user has the same name as the system stored procedure it will never be executed even if the user-created stored procedure is qualified with the owner/database name.
Database identifiers capitalization rules
For capitalization rules there are different guidelines also, for example Adventure Works database uses PascalCase for tables and columns, e.g. AddressType, BusinessEntity, JobCandidate, EmployeePayHistory because using PascalCase allows for the upper-case letter to mark the first letter of a new word or name. Thus there is no need to denote a new word with an underscore character.
Other guides suggest to only capitalize schema object names, and to use lower case for column names, e.g. payment, customer_id, department.
Variables naming rules
In addition to general naming standards variables names must begin with the “@” symbol. The “@@” symbols should not be used to prefix a variable. This stands for a SQL Server system global variable and using it to prefix a variable can affect performance.
Variables capitalization rules
Some capitalization standards suggest that variables should be written in camelCase, e.g. @lastName, @city, @employeeId.
Joe Celco suggests using lowercase letters for scalars such as column names, parameters, and variables, e.g. @indent, @list, @script.
Keywords capitalization rules
There are two types of keywords in SQL. Reserved keywords are a part of T-SQL grammar used by SQL Server, and non-reserved keywords are metadata names in the environment, and they are not likely to be used in a real application.
All capitalization standards suggest using uppercase letters e.g. ADD, EXCEPT, PERCENT.
Using ApexSQL Refactor, capitalization rules can be personalized and followed. Under the ApexSQL Refactor’s Options window, in the Capitalization tab, capitalization rules for keywords, types, identifiers, system functions, and variables can be set. There are options to use UPPERCASE, lowercase, Proper Case, or leave formatting as is:
Useful resources:
Books Online for SQL Server – Creating a Stored Procedure
Books Online for SQL Server – Transact – SQL Variables
Books Online for SQL Server – Reserved Keywords
February 13, 2014
