SQL Server 2019 Naming Conventions
SQL Server defines a set of rules (dos and don'ts) for naming SQL Server Objects called naming convention, but also gives the user to follow their own preferred style. It is advisable to follow a single naming convention for all database objects consistently.
Why Use Naming Conventions
Following a naming convention for tables, columns, and all other related database objects like views, stored procedures, indexes, triggers, etc., are important for the success and maintenance of a project. A database can have many tables and users working on it. By following a naming convention, you can spend less time finding what you need and helps in efficient database management.
SQL Server Database Object Naming Rules
- Names of database objects such as tables, columns, stored procedures, views, etc, can contain alphanumeric characters and must begin with an alphabet or an underscore.
- Case sensitivity is determined by the settings of the database in which the object is created. By default, SQL Server is not case sensitive.
- Avoid quotes while naming a database object.
- Use full English words and avoid abbreviations.
- Object names can contain numbers, #, $, @, but avoid using them.
- Certain symbols at the beginning of an object name have special significance in SQL Server.
- A name that starts with the 'at' (@) sign always denotes a local variable or parameter and cannot be used as a name for any other type of object.
- A name that begins with the number sign (#) denotes a temporary table or procedure.
- A name with double number signs (##) denotes a global temporary object. Microsoft doesn't recommend using the single number sign or double number sign to begin an object name.
- Some Transact SQL function names start with double at (@) signs. To avoid confusion, Microsoft recommends not using names that start with @.
- The Object name should not be a Transact SQL reserved word. SQL Server reserves both lowercase and uppercase versions of reserved words.
- Embedded spaces or special characters are not allowed.
- Supplementary characters are not allowed.
- Some system procedures and functions are given prefixes like 'sp_', 'xp_', or 'dt_' to signify that they are system objects and should be searched for in the master database. So it is better to avoid using these prefixes while creating database objects.
- There are a few commonly accepted styles to name a table & column in a database. It can be all 'lower case, 'UPPERCASE', 'Pascal Case' or 'camel Case'.
- Avoid using spaces between words for table names and columns as they will require square brackets and confuse scripts and procedures. Instead, use an underscore for joining multiple words. For example, EMPLOYEE_SKILL, employee_id instead of [EMPLOYEE ID].
- If a table represents a real-world entity, then use nouns to name the table. Example: EMPLOYEE, CITY, COUNTRY.
- Table names should be less than 126 characters.
- For table names, you can use either singular or plural. Example: EMPLOYEE OR EMPLOYEES.
- Avoid using prefixes like 'tbl_' for table names.
- While naming columns, use nouns that describe what is stored in the column. Example: FIRST_NAME, ADDRESS, EMAIL, PHONE. Avoid using words that are just data types like text or date.