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.	Subject	Prefix	Naming convention	Example
1	Database	db	db<name>	dbODS
2	Job	jb	jb<Jobname>	jbSSIS_RunLoadPackage
3	Linked Server	ls	ls<linkedservername>	lsServer01
4	Trigger(Delete)	td	td<name>	tdAppointment
5	Trigger(Insert)	ti	ti<name>	tiCustomer
6	Trigger(Update)	tu	tu<name>	tuAppointment

SQL Server Database objects

No.	Subject	Prefix	Naming convention	Example
1	Aggregate function	af	af<Name>	afTotalSales
2	Assemblies	as	ass<Name>	assDetermineIPs
3	Attribuut	-	<name>	PatientName
4	Database data file(s)	data#	<database>_data#	dbAdventureWorks_data1
5	Database log file	log	<database>_log	dbAdventureWorks_log
6	Default	df	df<name>	dfDate
7	Filegroup	fg	fg<name>	fgdbAdventureWorks_Index
				fgdbAdventureWorks_Data
				fgdbAdventureWorks_Log
8	Filestream	fs	fs<name>	fsInvoiceDoc
9	Foreign key	FKx_	FKx_<sourcetable>_<goaltable>_<fieldname(s)>#
10	Index (not unique, non clustered)	IDX_	IDX_<name>_<column>
11	Index (unique, non clustered)	UDX_	UDX_<name>_<column>
12	Index (not unique, clustered)	CIX_	CIX_<name>_<column>
13	Index (unique, clustered)	CUX_	CUX_<name>_<column>
14	Primary key	PK_	PK_<name>	PK_KlantID
15	Scalar valued function	scf	scf<name>	scfDetermineNumberOfCustomers
16	Schema	sch	sch<name>	schOrders
17	Stored procedure	usp	usp<projectname>_<name>	uspArchive_DetermineNumberOfOrders
18	Synoniems	syn	syn<name>
19	Table Valued function	tbl	tbl<name>	tblCustomers
20	Table Valued function	tvf	tvf<name>	tvfDeterminecustomers
21	Temporary Table	#tmp	#tmp<name>	#tmpPatient25Years
22	Type	udd	udd<name>	uddPostcode
23	Type	tp	tp<name>	tpPosInt
24	View	vw	vw<name>	vwGetOpenOrders

T-SQL

No.	Subject	Prefix	Naming convention	Example
1	Bigint	inb	inb<Variabelename>	inbPatientId
2	Binary	bin	bin<Variabelename>	binMessage
3	Bit	bit	bit<Variabelename>	bitOK
4	Char	chr	chr<Variabelename>	chrPatientname
5	Cursor	cur	cur<Variabelename>	curField
6	Datetime	dtm	dtm<Variabelename>	dtmInsertDate
7	Float	flt	flt<Variabelename>	fltXvalue
8	Integer	int	int<Variabelename>	intPatientID
9	Money	mny	mny<Variabelename>	mnyTotalAmount
10	Nchar	chrn	chrn<Variabelename>	chrnPatientname
11	Ntext	txtn	txtn<Variabelename>	txtMessage
12	Numeric of decimal	dec	dec<Variabelename>	decAmount
13	Nvarchar	chvn	chvn<Variabelename>	chvnPatientname
14	Real	rea	rea<Variabelename>	reaAmount
15	Smalldatetime	dts	dts<Variabelename>	dtsInsertDate
16	Smallint	ins	ins<Variabelename>	insSubcategorieID
17	Smallmoney	mns	mns<Variabelename>	mnsAmount
18	Sql_variant	var	var<Variabelename>	varFreevalue
19	Table variable	tav	tav<name>	tavCustomers
20	Text	txt	txt<Variabelename>	txtReport
21	Timestamp	tsp	tsp<Variabelename>	tspDeleteDate
22	Tinyint	iny	iny<Variabelename>	inyCategorieID
23	Uniqueidentifier	guid	guid<Variabelename>	guidKeu
24	Varbinary	biv	biv<Variabelename>	bivMessage
25	Varchar	chv	chv<Variabelename>	chvPatientname

Greetz
Hennie