Simple Object Naming
Naming programming objects. These rules are applicable to all SQL Server objects and a good idea in any environment.
- No spaces
- No prefixes in most situations. In SQL Server, if you want to group objects, put them in different schema.
- Only letters, numbers, $ and the underscore character _
- Always begin the name with a letter
- Be careful with $. Some programming languages assign a default meaning to $, especially at the biginning of a name.
- In SQL Server, object names can be 128 characters. Make object names descriptive, and don't try too hard to make them short.
- Tables, views, queries, forms, and controls should never have plural names.
- Plural names are reserved for collections (groups) of objects, not groups of data.
A Depreciated Object Naming Convention using prefixes:
This was a common object naming convention. Currently it is normally not used in new projects. This information is provided for people working on legacy projects which used this naming convention.
- The prefix denotes which section of the application (which module) the data objects is part of.
- Capitalization is Camel Case which means each new word is capitalized, except for the prefix, if one is used.
- Tables don't have prefixes, they have Schemas, which were called Owners in older SQL Server versions: dbo.UserAccount could be put in the Personnel schema: per.UserAccount.
- User Account Stored Procedures of the Personnel database module would be: perUserAccountCreate and perUserAccountRead or per.UserAccountCreate and per.UserAccountRead.
- Views are named like tables with a combination of the data provided and possibly the function that is applied to the data: per.UserAccountPermissions
- Both Views and Stored Procedures should be asigned to the same schema as the tables they use, especially in large projects.
Suggestions for Variable Data Type Naming
|String (Text)||str||strCity||Text to 255 characters|
|Date/Time||dtm||dtmCreated||Date and Time|
|Boolean||bln||blnIsNotNull||Yes/No or True/False, two values|
|Byte||byt||bytMonth||One Byte, values from 0 to +255|
|Integer||int or i||
|Two Bytes; values from -32,768 to +32,767; No fractions|
Four Bytes, values from
-2,147,483,648 to 2,147,483,647
|Single||sng||sngPopulation||Four Bytes single precision, floating point|
|Currency||cur||curTraded||Fifteen digits to the left, four to the left. Fixed decimal place.|
|Variant||vnt||vntUserInput||It can store numeric, string, date/time, Null, or Empty data|
Another Older Object Naming Convention
This is an obsolete naming convention. Forms of this convention were widely used when programming tools made it hard to tell if you were looking at a database, a table, or a query in the object viewer. This convention also works best with simple projects where there is only one module.
I have included this section because there are many projects "out there" using it. Also, some people still use this convention. As you can see, some of the prefixes are specific to development in Microsoft Access, which is natural, since it was Access developers who popularized this form of naming.
|Type of Object||Name Prefix||Example||Access Example|
Object Naming Conventions; Details
|Yes/No or boolean||ysn or bln|
My (Conrad Muller's) work on this page is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License.