Suggested Convention

In computer science theory, you can find several well-documented formal conventions. The most famous one is the Hungarian convention:

http://www.msdn.microsoft.com/isapi/msdnlib.idc?theURL=/library/techart/hunganotat.htm

I will present a convention that is rather informal and tailored for use in Transact-SQL. You do not have to follow it literally, but you should have a good reason to break any rule.

Tip 

Rules are made to be broken but only if the solution is tbereby improved.

Variables

Variable identifiers should consist of two parts:

• The base part, which describes the content of the variable

• The prefix, which describes the data type of the variable

Table A-1 shows data type abbreviations that should be used as prefixes.

Table A-1: Variable Prefixes

Data Type	Prefix	Example
char	chr	@chrFirstName
varchar	chv	@chvActivity
nchar	chrn	@chrnLastName
nvarchar	chvn	@chvnLastName
text	txt	@txtNote
ntext	txtn	@txtnComment
datetime	dtm	@dtmTargetDate
smalldatetime	dts	@dtsCompletionDate
tinyint	iny	@inyActivityld
smallint	ins	@insEquipmentTypeld
integer	int	@intAsset
bigint	inb	@inbGTIN
numeric or decimal	dec	@decProfit
real	rea	@reaVelocity
float	fit	@fltLength
smallmoney	mns	@mnsCost
money	mny	@mnyPrice
binary	bin	@binPath
varbinary	biv	@bivContract
image	img	@img Logo
bit	bit	@bitOperational
time stamp	tsp	@tspCurrent
uniqueidentif ier	guid	@guidOrderld
xml	xml	@xmlPart
sql_variant	var	@varPrice
cursor	cur	@curlnventory
table	tbl	@tbl Lease

Database Objects

Names of database objects should consist of two parts:

• The base part, which describes the content of the object

• The prefix, which describes the type of database object

Table A-2 shows database object abbreviations that should be used as prefixes.

Table A-2: Prefixes of Database Objects

Database Object	Prefix	Example
Table	(no prefix)	Equipment
Column	(no prefix)	Activityld
Synonym	(no prefix)	Eq
View	V	vActivities
Stored procedure	pr	prCompleteOrder
Trigger	tr	trOrderJU
Instead-of trigger	itr	trOrderJU
DDL trigger	dtr	trdShemaChangesLog
Default	df	dfToday
Rule	rul	rulCheckZIP
Index	ix	idxLastName
Primary key	pk	pk_Contactld
Foreign key	fk	fk_Order_OrderType
Primary XML index	pidx	pidx Equipment
Secondary XML index	sidx	sidxEquipment_Path
User-defined data type	udt	udt Phone
User-defined functions	fn	fnLastBusDay
Table-valued functions	fnt	fntDueDates

Note 

Tables, synonyms, and columns should not have prefixes describing the object type.

Triggers

Names of triggers should consist of three parts:

• The prefix, which implies the database object type

• The base part, which describes the table to which the trigger is attached

• The suffix, which shows modification statements (Insert, Update, and Delete)

The following is an example of a trigger name:

• trOrder_IU Insert and Update After trigger on Order table

If more than one trigger per modification statement is attached to the table, the base part should contain the name of the table and a reference to a business rule implemented by a trigger:

• trOrderCascadingDelete_D Delete trigger on Order table that implements cascading deletes of order items

• trOrderItemTotal_D Delete trigger on Order table that maintains a total of order item prices

To differentiate Instead-of triggers from standard triggers (After triggers), you should use a different naming convention for them. For example, you could use "itr" as a prefix:

• itrOrder_D Instead-of Delete trigger on the Order table

To differentiate DDL triggers from DML triggers, you can use the prefix "trd". Typical DDL operations are Create, Drop, and Alter, so we should use C, D, and A, respectively, as operation suffixes:

• trdLoginChanges_CA Server-level DDL trigger that logs Create and Alter statements on server logins

• trdTableView_DA Database-level trigger that logs statements that Drop or Alter tables and views

Stored Procedures

The base name of a stored procedure should usually be created from a noun followed by a verb to describe the process the stored procedure performs on an object, as in these examples:

• ap_Inventory_List

• ap_Lease_Close

I think that the noun-verb order is much more useful for grouping similar objects than verb-object order. In the case of noun-verb order, you will end up with manageable, topic-oriented groups of stored procedures. In the latter case, you will end up with much larger groups of procedures, sorted by operation (which is not very useful).

Note 

Some developers use the sp_ prefix in front of the hose name of a stored procedure. This prefix should he reserved for system stored procedures that reside in the master database and that are accessible from all databases.

You should also avoid computer-oriented or fuzzy names like these:

• ap_Data_Process

• ap_FeedData_Load

Names such as these are often a symptom of a poorly designed stored procedure.

If the procedure performs several tasks, all of those tasks should become part of the procedure name. It is okay to make procedure names longer than variable names. You should be able to pack a name into 20 to 40 characters.

Often, you need several stored procedures that query the same table but with different sets of input parameters. Since SQL Server does not support operator overloading, you must give them separate names. I often use By to delimit object name and search criteria:

• ap_InventoryByStatusId_List Lists records from the Inventory table with a specified Statusld

• ap_InventoryByEqType!cl_List Returns Inventory table records of a specified EqTypeld

• ap_InventoryByLocationByOwner_List Returns Inventory records with a specified location, owned by a specified user

In earlier versions of SQL Server, I often grouped stored procedures relating to the same module, application, or functional area using an application or module moniker in front of the object name. Monikers were typically short abbreviations (two to four, ideally three, characters), managed in a separate list:

• ap_AST_InventoryByEqType_List

• ap_AST_InventoryByLocationByOwner_List

• ap_RPT_InventoryByStatus_List

So the template for stored procedure names will be something like this:

      ap_MON_ObjectByCriteria_Operation 

Tip 

Since SQL Server 2005 has separated schemas from database objects, it makes more sense to group related stored procedures using scbemas. Scbemas allow you to group otber database objects as well.