SQL Server naming conventions

SQL Server Naming Conventions and Standards
 
1.0 Databases, Files, and File Paths
• The database name should accurately reflect the database content and function. All database names must be prefixed with the originating component's acronym--e.g., CBC_Medicare_Compare or EDG_Database_Tracking. Use both upper and lower case letters as appropriate. Do not use spaces in the name.
• File names must match the database name.
• If the database is being developed off site from CMS's central office, please create your databases in the default Microsoft directory structure. E.g., C:\Program Files\Microsoft SQL Server\ MSSQL\data\OIS_Personnel_Master_Data.MDF
2.0 Tables and Views
• Table names should accurately reflect the table's content and function. Do not use spaces in the name.
• View names follow the same conventions as table names, but should be prefixed with the literal 'VW'. E.g.,
vw1999NewRegulations
3.0 Columns
The standards below are applicable to all column names:
• Each column name must be unique within its table.
• Each column name must be derived from the business name identified during the business/data analysis process. For more information on deriving column names from business names, see Creating Physical Names for Elements and Columns in the Data Administration standards. If the column was not identified during the analysis of business data, it must still be given a spelled-out logical name and an abbreviated physical name.
• Do not use reserved or key words as object names.
In addition, if the data is going to be brought in-house to interact with other CMS computer systems, the following standards are applicable:
• The name can have a maximum of 18 characters.
• The name must include acceptable class and modifying words as specified in CMS's Data Administration standards.
4.0 Indexes
Indexes are named to indicate the table they are attached to and the purpose of the index.
Primary keys have a suffix of '_PK'.
• Foreign keys have a suffix of '_FKx' where x is a number that is incrementally assigned.
• Clustered indexes have a suffix of '_IDX'.
• All other indexes have a suffix of '_NDXx' where x is incrementally assigned.
Only one suffix per index may be appended. The application of the appropriate suffix should follow the following hierarchy: primary key, clustered index, foreign key, other index. E.g., an index that is both a primary key and clustered should have a suffix of '_PK'. It is good practice to index columns that are frequently used in a query's selection criteria.
5.0 Stored Procedures
• System level stored procedures are named using a prefix 'SP__' (two underscores) and a description of what the stored procedure does.
• All application level and user defined stored procedures are prefixed with the constant 'USP' with a description of what the stored procedure does. E.g., UspGetLastModifiedDate
6.0 Triggers
Triggers are named to indicate the table they are for and the type of trigger. The purpose of the trigger is identified in the prefix to the name. All triggers should be prefixed with the letter 'T', a letter(s) designating the type, an underscore, and the table name. The type should be designated as 'I' = insert, 'U' = update, 'D' = delete. E.g., ti_Orders (Insert trigger)
7.0 Variables
Variable identifiers for datatypes should consist of two parts:
• The base, which describes the content of the variable;
• The prefix, which describes the datatype of the variable
Correct prefixes for each datatype are shown in the table below.
Datatype     Prefix                          Example
Char         chr                                 @chrFirstName      
Varchar    chv                                @chvActivity
Nchar      chrn                                @chrnLastName
Nvarchar  chvn                               @chvnLastName
Text          txt                                   @txtNote
Ntext        txtn                                  @txtnComment
Datetime   dtm                                @dtmTargetDate
Smalldatetime dts                            @dtsCompletedDate
Tinyint   iny                                       @inyActivityID