SQL Server – Basic SQL Server Object Naming Convention Guidance

Microsoft doesn’t provide macro guidance for naming convention, however, sometimes it is useful to have a place to start.  Also, there are times when flexibility with naming conventions are necessary.  So, here is a quick set of SQL Server naming conventions, which may be helpful if you find yourself working with a customer who doesn’t have an established set of naming convention standards and you need to assemble a set fast.

Basic SQL Server Object Naming Convention Guidance

  1. Each project will have its own schema.
  2. Schema represents the project
  3. First letter of each word in table/Column starts with Uppercase.
  4. Put Underscore(_) between words of table/Column name
  5. No space between words in table/column name

Object Conventions

SQL Server objects

Subject

Prefix/Suffix

Naming convention

Example

Databasedbdb<name>dbODS
Jobjbjb<Jobname>jbSSIS_RunLoadPackage
Linked Serverlsls<linkedservername>lsServer01
Trigger(Delete)tdtd<name>tdAppointment
Trigger(Insert)titi<name>tiCustomer
Trigger(Update)tutu<name>tuAppointment

SQL Server Database objects

Subject

Prefix/Suffix

Naming convention

Example

Aggregate functionafaf<Name>afTotalSales
Assembliesasass<Name>assDetermineIPs
Attribuut<name>PatientName
Database data file(s)data#<database>_data#dbAdventureWorks_data1
Database log filelog<database>_logdbAdventureWorks_log
Defaultdfdf<name>dfDate
Dimension Table _Dim<<Table Name>>_DimGeography_Dim
Fact Table_Fact<<TableName>>_FactAccounts_Payable_Fact
Filegroupfgfg<name>fgdbAdventureWorks_Index
Filestreamfsfs<name>fsInvoiceDoc
Foreign keyFKx_FKx_<sourcetable>_<goaltable>_<fieldname(s)># 
Index (not unique, non clustered)IDX_IDX_<name>_<column> 
Index (unique, non clustered)UDX_UDX_<name>_<column> 
Index (not unique, clustered)CIX_CIX_<name>_<column> 
Index (unique, clustered)CUX_CUX_<name>_<column> 
Materialized ViewMV_MV_<Table name> 
Primary keyPK_PK_<name>PK_KlantID
Scalar valued functionscfscf<name>scfDetermineNumberOfCustomers
Schemaschsch<name>schOrders
Stored procedureuspusp<projectname>_<name>uspArchive_DetermineNumberOfOrders
Synonymssynsyn<name> 
Table tbltbl<name>tblCustomers
Table Valued functiontvftvf<name>tvfDeterminecustomers
Temporary Table#tmp#tmp<name>#tmpPatient25Years
Typeuddudd<name>uddPostcode
Typetptp<name>tpPosInt
Viewvw_VW_<table name>vwGetOpenOrders

Transact SQL (T-SQL)

Subject

Prefix/Suffix

Naming convention

Example

Bigintinbinb<Variabelename>inbPatientId
Binarybinbin<Variabelename>binMessage
Bitbitbit<Variabelename>bitOK
Charchrchr<Variabelename>chrPatientname
Code (Field)_Code<Variabelename>_CodeProgram_Code
Cursorcurcur<Variabelename>curField
Date (Field)_Dt<Variabelename>_DtCreated_Dt
Datetimedtmdtm<Variabelename>dtmInsertDate
Description (Field)_Desc<Variabelename>_DescPlan_Desc
Floatfltflt<Variabelename>fltXvalue
Identity (Field) _ID<Variabelename>_IDStatus_Rollup_ID
Integerintint<Variabelename>intPatientID
Moneymnymny<Variabelename>mnyTotalAmount
Ncharchrnchrn<Variabelename>chrnPatientname
Ntexttxtntxtn<Variabelename>txtMessage
Numeric of decimaldecdec<Variabelename>decAmount
Nvarcharchvnchvn<Variabelename>chvnPatientname
Realrearea<Variabelename>reaAmount
Smalldatetimedtsdts<Variabelename>dtsInsertDate
Smallintinsins<Variabelename>insSubcategorieID
Smallmoneymnsmns<Variabelename>mnsAmount
Sql_variantvarvar<Variabelename>varFreevalue
Surrogate key (Field) _ID<Variabelename>_IDPerson_ID
Table variabletavtav<name>tavCustomers
Texttxttxt<Variabelename>txtReport
Timestamptsptsp<Variabelename>tspDeleteDate
Tinyintinyiny<Variabelename>inyCategorieID
Uniqueidentifierguidguid<Variabelename>guidKeu
Varbinarybivbiv<Variabelename>bivMessage
Varcharchvchv<Variabelename>chvPatientname

Leave a Reply Cancel reply