Wayne Snyder posted on July 01, 2008 05:08
Many people get all tangled up in naming conventions. I have been tripped up by it also. But let me cut to the chase on naming. In the past many databases could only support object short object names. In SQL Server the length was 16 characters. IT came up with many rules, codes and abbreviations to keep the name short. This often led to cryptic names that made no sense to business. Prefixes, suffixes, class words – all part of this new language to allow short names. This is no longer required, since SQL Server allows 128 character names. The purpose of this standard is to eliminate the cryptic names. We are not encouraging the use of short or long names – the requirement is that business names be used.
All Object Names
- Use business terminology when naming database objects. No reference should be made to any applications, files, programs, operating systems, etc. that handle the object. Do not introduce any artificial, non-business abbreviations. For example, an attribute that the business calls Transformer Size, should not be named XFrmrSz or TrnsfrmrSize. It should be named Transformer_Size. Modifier Names should precede the noun. In the above example Transformer is the Modifier and Size is the noun.
- Do not use any database reserved words.
- SQL Server maximum length of object names is 128 characters.
- Do not use hyphens, spaces, and other special characters (such as "/).
- Pascal Case should be used, with underscores for separation of words. Pascal Case means the First letter of each word should be uppercase, and the remaining characters lower case. For example “business unit” is written as “Business_Unit”.
- Numerals should only be used to represent numbers. (ie. Use "2" to abbreviate "two".) Numerals may also be used in ordinals (such as "1st", "2nd", etc.).
- Table/View Names
- Names should be nouns and not verbs
- Do not use any Polish/hungarian type notations which identify the object type. For example, do not name objects tbl_customer, customer_tbl, vw_customer, or customer_vw.
- Data Warehouse Facts can be prefixed with Fact_
- Data Warehouse Dimensions will be named the business name, ie Customer, Equipment, Invoice, Employee, etc.
- Attribute Names
- Use business names. No additional class words, etc are required. The point is to name things appropriately so business users will understand what is contained within the attribute. Attribute names should be approved by the appropriate Data Steward.
- Do not add the table name as a prefix to the attribute name.
- Surrogate Key name should be <TableName>_Id
- Each table must have the following attributes:
- Create_Date DateTime NOT NULL
- Last_Updated_Date DateTime NOT NULL
Both attributes will be populated with the Greenwich Mean Time (GetUTCDate()) when a row is inserted. The Last_Updated_Date will be updated with the current date/time whenever a row is updated.
- Other Date Valued Attributes (If contains relevant time part as well) will be named
- <column name>_DateTime_Local (for local time) or
- <column Name>_DateTime_UTC (for Universal Time Code)
- If the field represents date only or time only values, then no suffix (UTC or Local) is required. You can name as <field name>_Date or <field name>_Time e.g Effective_Date or Efffective_Time
- Check Constraint Names
- All check constraints should be explicitly named.
- Names must indicate meaning/requirement of check constraint. This is because SQL Server uses the check constraint name in the error when the constraint is violated. Since these names must be unique across all tables, a common practice is to name the constraint using the follow syntax:
<TableName>_<ColumnName>_<Constraintdefinition>
Here is code to create a simple table with an unnamed check constraint.
CREATE TABLE test (id int check (id > 0))
GO
INSERT INTO test VALUES (-1)
GO
Here is the error returned on violation of the check constraint. This is of little value.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__test__id__4E1E9780". The conflict occurred in database "TestCodes", table "dbo.test", column 'id'.
The statement has been terminated.
Now let’s re-create the table with a more informative Check Constraint name.
CREATE TABLE test (id int
CONSTRAINT test_ID_Must_Be_Greater_Than_Zero check (id > 0))
GO
INSERT INTO test VALUES (-1)
GO
The error returned now is much more helpful
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "test_ID_Must_Be_Greater_Than_Zero". The conflict occurred in database "TestCodes", table "dbo.test", column 'id'.
The statement has been terminated.