Skip to main content
Naming Conventionssp_develop

What is your naming convention? From Steve Stedman

By May 25, 2022June 9th, 2023No Comments10 min read

Steve Stedman ask a great question “What is your naming convention?” in his blog article found here and is looking for feedback on our naming conventions.

I work on the SQL Server team at a consulting company. There I was asked to pull together a database development best practice guide for the data and software developer teams. Instead of just a document, I created a stored procedure named sp_Develop and a wiki website on how to use the tool and Best Practices & Potential Findings section. One of the sections is named Naming Conventions. With not being able to parse TSQL from TSQL there are many best practices that are not included in the stored procedure sp_Develop finding checks.

Below are Steve’s questions along with my answers

What is your naming convention for naming tables?

  • Do you always prefix your tables with something like tbl_?
    • Answer: Nope, we never use a descriptive prefix such as tbl_. Never use a descriptive prefix such as tbl_. This ‘reverse-Hungarian’ notation has never been a standard for SQL and clashes with SQL Server’s naming conventions. Some system procedures and functions were given prefixes sp_, fn_, xp_ or dt_ to signify that they were “special” and should be searched for in the master database first. See: Database Objects Should not Use Prefixes for more information.
  • Is there a company standard for how you should name tables?
    • Answer: Yep, Table and view names should be singular, for example, Customer instead of Customers. This rule is applicable because tables are patterns for storing an entity as a record – they are analogous to Classes serving up class instances. And if for no other reason than readability, you avoid errors due to the pluralization of English nouns in the process of database development. For instance, activity becomes activities, ox becomes oxen, person becomes people or persons, alumnus becomes alumni, while data remains data. See: Table and View Names Should be Singular for more information.
  • Do you always use lower case, always upper case, or camel case, or something else?
    • Answer: We do not have a single rule for everything. We utilize Redgate SQL Prompt with formatting styles that are shared amongst the staff. SQL code statements should be arranged in an easy-to-read manner. When statements are written all on one line or not broken into smaller easy-to-read chunks, it is hard to decipher. Your SQL code should be formatted in a consistent manner so specific elements like keywords, data types, table names, functions can be identified at a quick glance. See:Not Using SQL Formatting for more information.
      • Our data types should be lower cased to match the exact case that is in the SELECT * FROM sys.types; table. This will ensure collation differences won’t cause unexpected errors. See: Not Using lower case for Data Types for more information.
      • Keywords like SELECT, FROM, GROUP BY should be in UPPERCASE. See: Not Using UPPERCASE for Keywords for more information.
      • For all parts of the table name, use Pascal Case. PascalCase also reduces the need for underscores to visually separate words in names. See: PascalCase Usage for more information.
  • What is your standard for naming tables?
    • Answer: Don’t use reserved or future reserved words makes code more difficult to read, can cause problems to code formatters, and can cause errors when writing code. See: Reserved Words Usage for more information.
    • Answer: Beware of numbers in any object names, especially table names. It normally flags up clumsy denormalization where data is embedded in the name, as in Year2017, Year2018 etc. Usually the significance of the numbers is obvious to the perpetrator, but not to the maintainers of the system. See: Number Usage in Database Objects for more information.
    • Answer: Avoid using abbreviation if possible. Use Account instead of Acct and Hour instead of Hr. Not everyone will always agree with you on what your abbreviations stand for – and – this makes it simple to read and understand for both developers and non-developers. See: Using Abbreviation for more information.
  • Additional Table Naming Conventions
    • See: Column Naming – Click the link to the left to see all our column naming conventions.
    • See: Naming Foreign Key Relationships – Use the format of [FOREIGN-KEY-TABLE]_[PRIMARY-KEY-TABLE] in most cases. This gives you a quick view of the tables that are involved in the relationship. The first table named depends on the second table named.
    • See: Naming Primary Keys – Use the format of [TABLE-NAME]_[COLUMN-NAME].
    • For Naming Natural Keys See: Unique Constraint or Unique Indexes Usage.
    • See: Naming Constraint Usage – Use the format [TABLE-NAME]_[COLUMN-NAME]_[Default|Description]
    • See: Do not use System-Generated Object Names – Create logical names and do not let SQL Server name objects. If you do not specify an object name SQL Server will create one for you. This causes issues when comparing different environments that would have differently generated names.
    • See: Table Relationship Usage – Avoid, where possible, concatenating two table names together to create the name of a relationship (junction, reference, intersection, many-to-many) table when there is already a word to describe the relationship. e.g. use Subscription instead of NewspaperReader. When a word does not exist to describe the relationship use Table1Table2 with no underscores.
    • See: Primary Key Column Name – For columns that are the primary key for a table and uniquely identify each record in the table, the name should be [TableName] + Id (e.g. On the Make table, the primary key column would be MakeId).
    • See: Name Foreign Key Columns the Same as the Parent Table – Foreign key columns should have the exact same name as they do in the parent table where the column is the primary.
    • See: Special Characters Usage – Special characters should not be used in names. Using PascalCase for your table name allows for the upper-case letter to denote the first letter of a new word or name. Thus, there is no need to do so with an underscore character.
    • See: Number Usage in Database Objects – Beware of numbers in any object names, especially table names. It normally flags up clumsy denormalization where data is embedded in the name, as in Year2017, Year2018 etc.
    • See: Column Named Same as Table – Do not give a table the same name as one of its columns. A use case exception is for tables that store something like account numbers.
    • See: Using Abbreviation – Avoid using abbreviation if possible.
    • See: Non-Affirmative Boolean Name Use – Bit columns should be given affirmative boolean names like IsActive, IsDeleted, HasPermission, or IsValid so that the meaning of the data in the column is not ambiguous; negative boolean names are harder to read when checking values in T-SQL because of double-negatives (e.g. NOT IsNotDeleted).

What is your naming convention for naming indexes?

  • Do you like a prefix IDX_ for all indexes, or do you use NC_ or CL_ for nonclustered and clustered, or something completely different?
    • Answer: Nope, prefixes are not used in index names. We have done away with the “roman numeral” prefix type index naming. See: Improper Index Naming for more information.
  • Do you also include the table name or do you not use a table name in the index name?
    • Answer: Yep, Index Names should be [SchemaName_]TableName_Column1_Column2_Column3. See: Improper Index Naming for more information.
  • Do you add column names when you create indexes, or do you use some other description?
    • Answer: Yep, Index Names should be [SchemaName_]TableName_Column1_Column2_Column3. See: Improper Index Naming for more information.
  • How about included columns, do you add those in the name of the index?
    • Answer: Nope, just that there are includes. Index Names should indicate if there are included columns with [SchemaName_]TableName_Column1_Column2_Column3_Includes. See: Improper Index Naming for more information.
  • If you have an index that contains column names, do you rename it if you add more columns later?
    • Answer: Yep, the index gets renamed.
  • When you see the index name is it descriptive enough to know what it does? Do you always trust what it is named?
    • Answer: Yep, & Nope, it is always a best practice to check. You can script or view the properties of the index to confirm the index does what it is named.
  • How important is the name of the index really?
    • Answer: Good for a quick/summary review of the indexes but when I perform index tuning for clients, I do an in depth, deep dive and looking at each of the indexes.
  • What is your standard for naming indexes?

What is your naming convention for naming stored procedures?

  • Do you use sp_ as a prefix on your stored procedure names? Are you aware that is generally used for system stored procedures, and is processed differently than other naming conventions?
  • Do you prefix all procedures with anything like proc_ or sproc_? If so, why?
  • Do you prefix your procedures with a verb, like get, set, put, update, insert, etc?
  • When you are scrolling through the list of indexes in SSMS is it quick to find what you need, or do you carpel tunnel from the scroll wheel on your mouse with all the scrolling?
    • Answer: Yep, Stored procedures and functions should be named so they can be ordered by the table/business entity (ObjectAction) they perform a database operation on, and adding the database activity “Get, Update, Insert, Upsert, Delete, Merge” as a suffix, e.g., (ProductGet or OrderUpdate). See: Stored Procedures and Function Naming for more information. You always have the filter to narrow down your search in SSMS.
  • What is your standard for naming procedures?
  • Additional Stored Procedure Naming Conventions
    • See: Improper Parameter and Variable Naming – In addition to the general naming standards regarding no special characters, no spaces, and limited use of abbreviations and acronyms, common sense should prevail in naming variables and parameters; variable and parameter names should be meaningful and natural.All variables and parameters must begin with the @ symbol. Do not use @@ to prefix a variable as this signifies a SQL Server system global variable and will affect performance.All variables and parameter should be written in PascalCase, e.g. @FirstName or @City or @SiteId.Variable and parameter names should contain only letters and numbers. No special characters or spaces should be used. Parameter and variable and names should be named identically as the column names for the data they represent other than the @ symbol.

What is your naming convention for naming functions?

What is your naming convention for naming views?

  • Do you have a prefix for naming views, and if so why?
  • Do all of your views follow a standard or do you have different standards that have been followed over time.
    • Answer: Views mostly follow the table naming convention but might include a suffix like Detail, Lookup. See: Using Plural in Name for more information.
  • Are you views easy and quick to find using SSMS or other tools?
    • Answer: Yep, they are grouped by table/business entity (ObjectAction). You always have the filter to narrow down your search in SSMS.
  • What is your standard for naming views?

General

  • Are there any naming conventions that you really like?
    • Answer: Yep, all the naming conventions found in sp_Develop Naming Convention section.
  • Are there any naming conventions that you just cannot stand?
    • Answer: Most of the naming conventions on client databases. It is always recommended to adapt the naming and coding conventions of inherited projects.
  • If you were starting from scratch at a new company on a new project in a brand new database, would you use the same naming conventions that you use today?
    • Answer: Yep.