Architecture, Standards

T-SQL Naming

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.

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.

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

Tagged , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.