zaterdag 16 april 2011

Best practices SQL Server naming conventions

Hi,

Today a post about SQL Server naming conventions. I've gathered these naming conventions in the last couple of years and i've implemented them at projects at my customers. This is the fourth post in a serie of best practice posts (part I, part II and part III). As said, this post is about naming conventions and these naming conventions can be subdivided in server objects-, database objects- and T-SQL naming conventions.

There are a lot advantages when you stick to them. One anecdote about a customer who said to me: "a naming convention would be very helpful because when i'm building queries i don't see the difference between tables and views, at first glance.". Another remark about naming convention is that you can combine the naming conventions with other naming conventions e.g. tblODS<table> or tblDimCustomer. This way you can combine your technical naming convention with a more functional naming convention.

SQL Server Server objects

No.SubjectPrefixNaming conventionExample
1Databasedbdb<name>dbODS
2Jobjbjb<Jobname>jbSSIS_RunLoadPackage
3Linked Serverlsls<linkedservername>lsServer01
4Trigger(Delete)tdtd<name>tdAppointment
5Trigger(Insert)titi<name>tiCustomer
6Trigger(Update)tutu<name>tuAppointment


SQL Server Database objects

No.SubjectPrefixNaming conventionExample
1Aggregate functionafaf<Name>afTotalSales
2Assembliesasass<Name>assDetermineIPs
3Attribuut<name>PatientName
4Database data file(s)data#<database>_data#dbAdventureWorks_data1
5Database log filelog<database>_logdbAdventureWorks_log
6Defaultdfdf<name>dfDate
7Filegroupfgfg<name>fgdbAdventureWorks_Index
fgdbAdventureWorks_Data
fgdbAdventureWorks_Log
8Filestreamfsfs<name>fsInvoiceDoc
9Foreign keyFKx_FKx_<sourcetable>_<goaltable>_<fieldname(s)># 
10Index (not unique, non clustered)IDX_IDX_<name>_<column>
11Index (unique, non clustered)UDX_UDX_<name>_<column>
12Index (not unique, clustered)CIX_CIX_<name>_<column>
13Index (unique, clustered)CUX_CUX_<name>_<column>
14Primary keyPK_PK_<name>PK_KlantID
15Scalar valued functionscfscf<name>scfDetermineNumberOfCustomers
16Schemaschsch<name>schOrders
17Stored procedureuspusp<projectname>_<name>uspArchive_DetermineNumberOfOrders
18Synoniemssynsyn<name>
19Table Valued functiontbltbl<name>tblCustomers
20Table Valued functiontvftvf<name>tvfDeterminecustomers
21Temporary Table#tmp#tmp<name>#tmpPatient25Years
22Typeuddudd<name>uddPostcode
23Typetptp<name>tpPosInt
24Viewvwvw<name>vwGetOpenOrders

T-SQL

No.SubjectPrefixNaming conventionExample
1Bigintinbinb<Variabelename>inbPatientId
2Binarybinbin<Variabelename>binMessage
3Bitbitbit<Variabelename>bitOK
4Charchrchr<Variabelename>chrPatientname
5Cursorcurcur<Variabelename>curField
6Datetimedtmdtm<Variabelename>dtmInsertDate
7Floatfltflt<Variabelename>fltXvalue
8Integerintint<Variabelename>intPatientID
9Moneymnymny<Variabelename>mnyTotalAmount
10Ncharchrnchrn<Variabelename>chrnPatientname
11Ntexttxtntxtn<Variabelename>txtMessage
12Numeric of decimaldecdec<Variabelename>decAmount
13Nvarcharchvnchvn<Variabelename>chvnPatientname
14Realrearea<Variabelename>reaAmount
15Smalldatetimedtsdts<Variabelename>dtsInsertDate
16Smallintinsins<Variabelename>insSubcategorieID
17Smallmoneymnsmns<Variabelename>mnsAmount
18Sql_variantvarvar<Variabelename>varFreevalue
19Table variabletavtav<name>tavCustomers
20Texttxttxt<Variabelename>txtReport
21Timestamptsptsp<Variabelename>tspDeleteDate
22Tinyintinyiny<Variabelename>inyCategorieID
23Uniqueidentifierguidguid<Variabelename>guidKeu
24Varbinarybivbiv<Variabelename>bivMessage
25Varcharchvchv<Variabelename>chvPatientname


Greetz
Hennie

2 opmerkingen:

  1. Hello Hennie,

    Again an excellent blog post.
    You might want to have a look at the following sites to get more info collected.

    http://www.indiana.edu/~dss/Services/Naming/nvgintro.html

    http://www.lifecycle-toolkit.com/tools/nmngcnv/modelstd/namest.htm

    http://www.lifecycle-toolkit.com/tools/NmngCnv/dbstnds/namtoc.htm

    BeantwoordenVerwijderen
  2. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog on. SQL server dba Online Training

    BeantwoordenVerwijderen