This standard should provide guidance for developers who are naming object in T-SQL.
Philosophy
We want object to have simple to understand and consistent naming across all T-SQL in an enterprise. This will not only help your quality but speed up the development life cycle of T-SQL projects.
Overall All Concepts
This are concepts that apply to all T-SQL objects
- The should be PascalCase starting with a capital letter
- Example: AwesomePeople
Databases
Names of Databases should reflect the function of the database. Its name should direct the users to understand its function and purpose from its name.
- The database name should not have abbreviations
Tables
Names of tables should reflect the function of the table. It’s name should direct the user to understand the function and purpose from its name.
- Name Structure: FunctionType
- Function: This is the function of the table.
- Type: This is required if the table is one of the types listed below.
- Example: AccoutDim
- Do not include the database name in the table name
- Types to be included in name
- Dimension Table – Dim
- Fact Table – Fact
- Bridge – Bridge
- FactLessFactTable – Factless
Views
Names of views should reflect the function of the view. Its name should direct the user to understand the function and purpose from its name.
- Name Structure: vwFunctionType
- All views should start with the prefix ‘vw’
- Function: This is the function of the view
- Type: This is required if the view is one of the types listed below.
Stored Procedures
Names of stored procedures should reflect the function of the stored procedures. Its name should direct the user to understand the function and purpose from its name.
- Name Structure: spFunctionAction
- All stored procedures should start with sp
- Function: This is the function of the SP or Object that is working on
- Action: This is options if the SP has an action like Get or Set
Columns
Names of columns should reflect the function of the column. Its name should direct the user to understand the function and purpose from its name.
- The primary key column if it is an identifier it should have the table name
- Example: AddressDimId
Indexes
Names of indexes should reflect the function of the index. Its name should direct the user to understand the function and purpose from its name.
- Name Structure: indextype_tablename_column1_column2_columnN
- Index Type prefix should follow the list below.
- PK_ for primary keys
- UK_ for unique keys
- IX_ for non-clustered non-unique indexes
- UX_ for unique indexes
- CCI_ for clustered column store indexes
- Tablename should be the name of the table the index is applied too.
- Then the rest of the name should all the columns that are included in the index.
- Index Type prefix should follow the list below.
Triggers
Names of triggers should reflect the function of the trigger. Its name should direct the user to understand the function and purpose from its name.
- Name Structure: trgTableAction
- All triggers should start with the trg prefix
- The name of the table should be included next in the name of the trigger
- Action: This should include both the functional action of the trigger as well as the table action
- Example: trgAddressAuditInsert
Variables
Names of variables should reflect the function of the variable. Its name should direct the user to understand the function and purpose from its name.
- Name Structure: scopeFunction
- Scope: This is the scope of the variable.
- Parameter: p
- Local: l
- Function: This is function of the variable. If it is pulled from a column in a table it should match that as the function. If it is an aggregation or modification of the data it should not be the same as the column to avoid confusion.
- Scope: This is the scope of the variable.
Functions
Names of functions should reflect the function of the function. Its name should direct the user to understand the function and purpose from its name.
- Name Structure: fnFunction
- All function should start with the fn prefix
- Function: This should include the function of the function. It should be clear what it is doing and what it is for.
SSIS Packages
Names of packages should reflect the function of the package. Its name should direct the user to understand the function and purpose from its name.
- The overall name of the SSIS package should always be descriptive of the purpose of the package. It should be clear what it is for and why it exists.
The following are recommended beginning names of tasks
Task | Prefix |
For Loop Container | FLC |
Foreach Loop Container | FELC |
Sequence Container | SEQC |
ActiveX Script | AXS |
Analysis Services Execute DDL | ASE |
Analysis Services Processing | ASP |
Bulk Insert | BLK |
Data Flow | DFT |
Data Mining Query | DMQ |
Execute DTS 2000 Package | EDPT |
Execute Package | EPT |
Execute Process | EPR |
Execute SQL | SQL |
File System | FSYS |
FTP | FTP |
Message Queue | MSMQ |
Script | SCR |
Send Mail | SMT |
Transfer Database | TDB |
Transfer Error Messages | TEM |
Transfer Jobs | TJT |
Transfer Logins | TLT |
Transfer Master Stored Procedures | TSP |
Transfer SQL Server Objects | TSO |
Web Service | WST |
WMI Data Reader | WMID |
WMI Event Watcher | WMIE |
XML | XML |
Expression | EXPR |
The following should be used for components
Component | Prefix |
DataReader Source | DR_SRC |
Excel Source | EX_SRC |
Flat File Source | FF_SRC |
OLE DB Source | OLE_SRC |
Raw File Source | RF_SRC |
XML Source | XML_SRC |
Aggregate | AGG |
Audit | AUD |
Character Map | CHM |
Conditional Split | CSPL |
Copy Column | CPYC |
Data Conversion | DCNV |
Data Mining Query | DMQ |
Derived Column | DER |
Export Column | EXPC |
Fuzzy Grouping | FZG |
Fuzzy Lookup | FZL |
Import Column | IMPC |
Lookup | LKP |
Merge | MRG |
Merge Join | MRGJ |
Multicast | MLT |
OLE DB Command | CMD |
Percentage Sampling | PSMP |
Pivot | PVT |
Row Count | CNT |
Row Sampling | RSMP |
Script Component | SCR |
Slowly Changing Dimension | SCD |
Sort | SRT |
Term Extraction | TEX |
Term Lookup | TEL |
Union All | ALL |
Unpivot | UPVT |
Data Mining Model Training | DMMT_DST |
DataReader Destination | DR_DST |
Dimension Processing | DP_DST |
Excel Destination | EX_DST |
Flat File Destination | FF_DST |
OLE DB Destination | OLE_DST |
Partition Processing | PP_DST |
Raw File Destination | RF_DST |
Recordset Destination | RS_DST |
SQL Server Destination | SS_DST |
SQL Server Mobile Destination | SSM_DST |
diary writing flickr photo by freddie boy shared under a Creative Commons (BY-SA) license