· 7 years ago · Nov 30, 2018, 12:50 PM
1# VB Code Guidelines
2
3<!-- TOC -->
4
5- [VBA Code Guidelines](#vba-code-guidelines)
6 - [General Advice](#general-advice)
7 - [Parameters](#parameters)
8 - [General errors](#general-errors)
9 - [Variables](#variables)
10 - [General](#general)
11 - [Declaring](#declaring)
12 - [Comments](#comments)
13 - [Variants](#variants)
14 - [Dates](#dates)
15 - [General Naming Conventions](#general-naming-conventions)
16 - [General](#general-1)
17 - [Prefix](#prefix)
18 - [Tag](#tag)
19 - [Base name](#base-name)
20 - [Qualifiers](#qualifiers)
21 - [Arrays](#arrays)
22 - [Constants](#constants)
23 - [API Declaration](#api-declaration)
24 - [Use unique alias names](#use-unique-alias-names)
25 - [Form, Class & Module Naming](#form-class--module-naming)
26 - [Internal Naming](#internal-naming)
27 - [File naming](#file-naming)
28 - [Object instance naming](#object-instance-naming)
29 - [Notes](#notes)
30 - [Naming Procedures/Functions/Parameters](#naming-proceduresfunctionsparameters)
31 - [Function Names](#function-names)
32 - [Function return values](#function-return-values)
33 - [Parameters](#parameters-1)
34 - [Naming Controls](#naming-controls)
35 - [Introduction](#introduction)
36 - [Control tags](#control-tags)
37 - [Naming menu items](#naming-menu-items)
38 - [**Naming Data Access Objects**](#naming-data-access-objects)
39 - [ADO](#ado)
40 - [ADO objects](#ado-objects)
41 - [MS Access objects](#ms-access-objects)
42 - [Layout](#layout)
43 - [Indentation – tab width](#indentation--tab-width)
44 - [Indentation - general](#indentation---general)
45 - [Commenting Code](#commenting-code)
46 - [Comments](#comments-1)
47 - [Commenting code when doing maintenance work](#commenting-code-when-doing-maintenance-work)
48 - [Etiquette when commenting code](#etiquette-when-commenting-code)
49 - [Pre-compilation commands](#pre-compilation-commands)
50 - [Error Handling](#error-handling)
51 - [Generic error handler](#generic-error-handler)
52 - [Error handling labels](#error-handling-labels)
53 - [SQL Server stored procedures](#sql-store-procedures)
54 - [Overview](#overview)
55 - [Commenting Code ](#Commenting-Code )
56 - [Naming conventions](#Naming-conventions)
57 - [Database Coding Standard and Guideline](#database-coding-standard-and-guideline)
58 - [Naming](#naming)
59 - [Structure](#structure)
60 - [Formatting](#formatting)
61 - [#Reference](#reference)
62<!-- /TOC -->
63
64## General Advice
65
66- The first character of that name must be an alphabetic character
67- Begin each separate word in a name with a capital letter, as in ```FindLastRecord``` and ```RedrawMyForm```.
68- Begin function and method names with a verb, as in ```InitNameArray``` or ```CloseDialog```.
69- Begin class, structure, module, and property names with a noun, as in ```EmployeeName``` or ```CarAccessory```.
70- Begin interface names with the prefix "I", followed by a noun or a noun phrase, like IComponent, or with an adjective describing the interface's behavior, like IPersistable. Do not use the underscore, and use abbreviations sparingly, because abbreviations can cause confusion.
71- Begin event handler names with a noun describing the type of event followed by the "EventHandler" suffix, as in "MouseEventHandler".
72- In names of event argument classes, include the "EventArgs" suffix.
73- If an event has a concept of "before" or "after," use a suffix in present or past tense, as in "ControlAdd" or "ControlAdded".
74- For long or frequently used terms, use abbreviations to keep name lengths reasonable, for example, "HTML", instead of "Hypertext Markup Language". In general, variable names greater than 32 characters are difficult to read on a monitor set to a low resolution. Also, make sure your abbreviations are consistent throughout the entire application. Randomly switching in a project between "HTML" and "Hypertext Markup Language" can lead to confusion.
75- Avoid using names in an inner scope that are the same as names in an outer scope. Errors can result if the wrong variable is accessed. If a conflict occurs between a variable and the keyword of the same name, you must identify the keyword by preceding it with the appropriate type library. For example, if you have a variable called Date, you can use the intrinsic Date function only by calling DateTime.Date.
76
77## Parameters
78
79Avoid confusion over ByVal and ByRef. Be aware of the default for
80parameters being ByRef. Be explicit when passing parameters.
81
82- [Force] Only use ByRef where you intend to modify the parameter and
83 pass the change back to the Caller.
84- [Force] Pass parameter ByVal if they are not to be changes
85- [Suggest] Explicitly use ByRef in an input parameter is to be
86 changed, but watch for signs to redesign.
87
88Pass by Reference example:
89
90```vba
91Private Sub ChangeRefValue()
92 Dim intX As Integer
93 intX = 1
94 Debug.Print intX ' print value of intX is 1
95
96 Call ChangeValueByRef(intX)
97
98 Debug.Print intX ' print value of intX is 6
99End Sub
100
101Sub ChangeValueByRef(ByRef intY As Integer)
102 intY = intY + 5
103End Sub
104```
105
106Pass by Value example:
107
108```vba
109Public Sub Load(ByVal strName As String, ByVal strPhone As String)
110```
111
112## General errors
113
114Error handling must be used wherever practicable i.e. within each
115procedure. Use On Error Goto ErrHandler Handle errors where they
116occur. This may involve handling the error and raising it to the
117client code.
118
119## Variables
120
121### General
122
123[Force] Where global variables are used, they must all be defined in one
124module.
125
126[Force] Hard-coded(Magic) numbers and strings should be made into constants.
127
128[Force] Use explicit data casting fcuntion `Cstr()`, `CDate()`, `Cbool()` etc.
129
130### Declaring
131
132[Force] Variables must be dimensioned on separate lines, and should specify a
133datatype (except where this is not possible – as when using certain
134scripting languages).
135
136### Comments
137
138[Suggest] All variables must be declared at the top of each procedure or module
139and must ideally be grouped so that all variable types are placed
140together.
141
142### Variants
143
144[Suggest] Variants may be used where appropriate (e.g. to hold arrays returned
145by a function, or where Nulls may be encountered), but alternative
146data types should be used where possible.
147
148[Suggest] Advoid using Variants data type unless absolutely necessary.
149Variants are slower then native types, when there's large dataset
150or a big macro project.
151
152### Dates
153
154[Force] Where dates are displayed to users you should avoid ambiguous formats
155where either years or days vs. months might be confused (such as
156DD/MM/YY), however the ultimate decision maker on this issue is the
157customer.
158
159Where dates are being handled “behind the scenes†care should be taken
160to avoid UK/US format confusion. Particular care should be taken when
161including UK-format dates in literal SQL strings (where the target
162Microsoft application may expect dates to be in US format). Where
163there is the slightest possibility of doubt pass the year, month and
164day parts separately into DateSerial, of format them in the
165universally acceptable ISO format YYYY-MM-DD.
166
167## General Naming Conventions
168
169### General
170
171Object names are made up of four parts: prefix tag base name qualifier
172The four parts are assembled as follows:
173[prefixes]tag[BaseName][qualifier] Note: The brackets denote that
174these components are optional and are not part of the name.
175
176### Prefix
177
178Prefixes and tags are always lowercase so your eye goes past them to
179the first uppercase letter where the base name begins. This makes the
180names more readable. The base and qualifier components begin with an
181uppercase letter.
182
183| Prefix | Use | Notes |
184| ------ | ------------------ | --------------------------------- |
185| None | Local to procedure | No scope prefix as in: dblMaximum |
186| m\_ | Module level scope | m_strPolicyHolder |
187| g\_ | Global scope | g_intCarsLast |
188
189### Tag
190
191The tag is the only required component, but in almost all cases the
192name will have the base name component since you need to be able to
193distinguish two objects of the same type.
194
195| Variable type | Tag | Notes |
196| ----------------- | --- | ------------- |
197| Boolean | bln | blnFound |
198| Byte | byt | bytRasterData |
199| Currency | cur | curRevenue |
200| Date (Time) | dat | datStart |
201| Double | dbl | dblTolerance |
202| Enum | enm | enmColours |
203| Integer | int | intQuantity |
204| Long | lng | lngDistance |
205| Single | sng | sngAverage |
206| String | str | strFName |
207| User-defined type | udt | udtEmployee |
208| Variant | var | varCheckSum |
209
210[suggest] To avoid defining an ambiguous variable, it is strongly suggest to use
2113-letter abbreviations instead of using a single-letter abbreviations
212
213Positive Example:
214```vba
215Dim intProductID As Integer
216```
217
218Negative Example:
219```vba
220Dim iProductID As Integer ' Too short abbreviative tag definition
221Dim orderID As Integer ' Meaningless definition
222```
223
224### Base name
225
226The base name succinctly describes the object, not its class. That is,
227a base name for a variable for an invoice form must be InvoiceEntry
228not InvoiceForm as the tag will describe the object. The base name is
229composed in the form Noun[Verb]. For example, in the variable name
230blnColourMatch "ColourMatch" is the base name. Naming variables in
231this way helps to keep them grouped together in documentation and
232cross-referencing because they will be sorted together alphabetically.
233
234### Qualifiers
235
236Object qualifiers may follow a name and further clarify names that are
237similar. Continuing with our previous example, if you kept two indexes
238to an array, one for the first item and one for the last this entails
239two qualified variables such as intColourMatchFirst and
240intColourMatchLast. Other examples of qualifiers:
241
242| Usage | Qualifier | Example |
243| ----------------------- | --------- | --------------- |
244| Current element of set | Cur | intCarsCur |
245| First element of set | First | intCarsFirst |
246| Last element of set | Last | intCarsLast |
247| Next element of set | Next | strCustomerNext |
248| Previous element of set | Prev | strCustomerPrev |
249| Lower limit of range | Min | strNameMin |
250| Upper limit of range | Max | strNameMax |
251| Source | Src | lngBufferSrc |
252| Destination | Dest | lngBufferDest |
253
254### Arrays
255
256[Force] Array names must be prefixed with "a" or "arr". The upper and lower
257bounds ofthe array must be declared explicitly (unless they’re not known at
258design-time).
259
260Positive Example:
261```vba
262Dim astrMonths(1 To 12) As String
263```
264
265Negative Example:
266```vba
267Dim strMonths(1 To 12) As String
268```
269
270### Constants
271
272Each word must be capitalised and the words separated with an
273underscore. The base name must be a description of what the constant
274represents.
275
276Example:
277
278```vba
279User defined constant: g_intERR_INVALID_NAME
280Visual Basic: vbArrowHourglass
281```
282
283## API Declaration
284
285API declarations must be laid out so that they are easily readable on
286the screen.
287
288```vba
289Public Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" _
290 (ByVal lpApplicationName As String, _
291 ByVal lpKeyName As Any, _
292 ByVal lpString As Any, _
293 ByVal lpFileName As String) As Long
294```
295
296### Use unique alias names
297
298In VB you can call external procedures in DLLs when you know the entry
299point (the name of the function in the DLL). However, the caveat is
300that you can only declare the external procedure once. If you load a
301library that calls the same Windows API that your module calls, you
302will get the infamous error, “Tried to load module with duplicate
303procedure definition.â€
304
305```
306Declare smg_GetActiveWindow Lib "Kernel" Alias _
307 "GetActiveWindo" () As Integer
308```
309
310## Form, Class & Module Naming
311
312### Internal Naming
313
314(i.e. the name assigned to the module within the VB Properties)
315
316| Module Type | Prefix | Example |
317| ---------------- | ------ | ------------ |
318| Form | frm | frmLogon |
319| Standard module | mod | modUtilities |
320| Class module | C | CPerson |
321| Collection class | C | CPersons1 |
322| Interface class | I | IPerson |
323
324### File naming
325
326(i.e. the name assigned to the module when saving the physical file)
327
328| Module Type | Prefix | Example |
329| ---------------- | ------ | ---------------- |
330| Form | frm | frmLogon.frm |
331| Standard module | mod | modUtilities.bas |
332| Class module | C | CPerson.cls |
333| Collection class | C | CPersons.cls1 |
334| Interface class | I | IPerson.cls |
335
336### Object instance naming
337
338(i.e. the name assigned when declaring a variable based on the form or
339class)
340
341| Instance of | Prefix | Example |
342| ----------- | ------ | ---------- |
343| Form | frm | frmLogon |
344| Class | obj | objPerson |
345| Collection | obj | objPersons |
346
347### Notes
348
3491 Classes which hold collections should have the same “C†prefix as
350any other classes, but should have a plural name (based on the type of
351objects held in the collection. E.g. a class to hold a single person
352would be named CPerson, whereas a collection of Person objects would
353be named CPersons.
354
355## Naming Procedures/Functions/Parameters
356
357## Function Names
358
359Tags should not be prefixed to Function or Sub names, but **should**
360be appended to the parameters of these routines. For example:
361
362_**Correct approach for internal function:**_
363
364```vba
365Private Function TotalUp(ByVal sngSubTotal As Single) As Integer
366```
367
368### Function return values
369
370Function return values should usually be held in a temporary variable
371and then assigned to the function variable at the end of the routine.
372This has two benefits. The code is not specific to the name of the
373function so portability is aided when cutting and pasting part of the
374function code elsewhere; also the value of the function variable may
375be used in calculations, otherwise a recursive call would be
376generated. Example:
377
378```vba
379Private Function Example(ByVal argintA as Integer) as Single
380 Dim sngRetVal as Single
381
382 ' Set default value
383 sngRetVal = 0
384
385 <code block>
386
387 ' Set the Function value
388 Example = sngRetVal
389End Function
390```
391
392### Parameters
393
394Should you find it useful, you may also prefix parameter names with
395arg to avoid confusion between variables passed as parameters and
396those local to the subroutine . Example:
397
398```vba
399Private Function DoSomething(ByVal argstrMessage as String) as String
400```
401
402However, should you choose to adopt this standard it must be applied
403consistently across the entire project
404
405## Naming Controls
406
407### Introduction
408
409Controls must be named with uniform prefixes strictly adhering to the
410following list.
411
412### Control tags
413
414| Object Type | Tag | Notes |
415| ----------------------------- | ---- | --------------- |
416| Check box | chk | chkReadOnly |
417| Combo box, drop-down list box | cbo | cboEnglish |
418| Command button | cmd | cmdExit |
419| Common dialog | dlg | dlgFileOpen |
420| Control | ctl | ctlCurrent |
421| Form | frm | frmEntry |
422| Frame | fra | fraLanguage |
423| Grid | grd | grdPrices |
424| Image | img | imgIcon |
425| Key status | key | keyCaps |
426| Label | lbl | lblHelpMessage |
427| Line | lin | linVertical |
428| List box | lst | lstPolicyCodes |
429| Menu | mnu | mnuFileOpen |
430| Report | rpt | rptQtr1Earnings |
431| Shape | shp | shpCircle |
432| Text box | txt | txtLastName |
433| True DBGrid | tdbg | tdbgRecords |
434| Timer | tmr | tmrAlarm |
435| ImageList | ils | ilsAllIcons |
436| Toolbar | tlb | tlbActions |
437| TabStrip | tab | tabOptions |
438| ListView | lvw | lvwHeadings |
439
440### Naming menu items
441
442The number of menu options can be great, so it is recommended that
443there be a standard for the names given to menus. The tag for any menu
444item whether an option or title is mnu. Prefixing must ideally
445continue beyond the initial prefix. The first prefix after mnu is the
446menu bar title followed by the option then any subsequent option.
447
448Example:
449
450```
451Top level menu item – mnuFile
452Menu sub item – mnuFileSave
453```
454
455## **Naming Data Access Objects**
456
457### ADO
458
459If you include references to both ADO and DAO in the same project you
460must explicitly specify which object model you wish to use when
461declaring variables. Example:
462
463```vba
464Dim cnnStore As ADODB.Connection
465Dim cnnOther As DAO.Connection
466```
467
468### ADO objects
469
470| Object Type | Tag | Example |
471| ----------- | --- | ----------- |
472| Command | cmd | cmdBooks |
473| Connection | cnn | cnnLibrary |
474| Parameter | prm | prmTitle |
475| Error | err | errLoop |
476| Recordset | rst | rstForecast |
477
478### MS Access objects
479
480The following is a suggested naming convention for use with MS Access
481objects – you may find it useful for larger Access projects which have
482many objects within the same database.
483
484| Object Type | Tag | Example |
485| ------------------ | ---- | ----------------- |
486| Table | tbl | tblCustomer |
487| Query (select) | qry | qryOverAchiever |
488| Query (append) | qapp | qappNewProduct |
489| Query (crosstab) | qxtb | qxtbRegionSales |
490| Query (delete) | qdel | qdelOldAccount |
491| Query (make table) | qmak | qmakShipTo |
492| Query (update) | qupd | qupdDiscount |
493| Form | frm | frmCustomer |
494| Form (dialog) | fdlg | fdlgLogin |
495| Form (message) | fmsg | fmsgWait |
496| Form (subform) | fsub | fsubOrder |
497| Report | rpt | rptInsuranceValue |
498| Report (subreport) | rsub | rsubOrder |
499| Macro (menu) | mmnu | mmnuEntryFormFile |
500| Module | mod | modBilling |
501
502## Layout
503
504### Indentation – tab width
505
506When working in a VB or VBA design environment you **must** have the
507**Tab Width** set to 4 (see the Editor tab in Tools > Options). This
508is the default VB setting, and using it ensures compatibility when
509code is worked on by more than one person.
510
511### Indentation - general
512
513Code must be indented consistently adhering to the following rules:
514
515- Declarations must not be indented.
516- On Error statements and line labels/numbers must not be indented.
517- Start code indented to one tab stop.
518- Code within If-Else-EndIf, For-Next, Do While/Until and any other
519 loops must be indented a further tab stop within the body.
520- Code between add/edit and update statements must be indented a
521 further tab stop.
522- Case statements must be indented to one stop after the Select Case.
523 Code following the Case statements must be indented a further Tab
524 stop.
525- Code between With and End With statements must be indented by one
526 tab stop.
527- Code within error trap must be indented by to one tab stop. Example
528
529```vba
530Dim strTest as String
531Dim wrk as Workspace
532On Error Goto ErrHandler
533 If strTest = "" Then
534 strTest = "Nothing"
535 Else
536 strTest = ""
537 EndIf
538
539 Do While Not rst.EOF
540 rst.Add
541 rst(0) = strTest
542 rst.Update
543 Loop
544
545 Select Case strTest
546 Case ""
547 <code block>
548 Case Else
549 <code block>
550 End Select
551ExitHere:
552 Exit Sub
553ErrHandler:
554 Resume ExitHere
555```
556
557## Commenting Code
558
559### Comments
560
561Remember the following points:
562
563- Code must be commented appropriately. The goal should be to improve
564 understanding and maintainability of the code.
565- Comments should explain the reasoning behind the code. It may be
566 obvious to the original developer what a piece of code does but
567 somebody reading it may have no idea why it has to be there. When
568 you write a piece of code, imagine someone else having to read
569 through it 3 months later. Will it make sense to them?
570- Important variable declarations may include an inline comment
571 describing the use of the variable being declared.
572
573Example:
574
575```vba
576Dim strLookUp as String 'Accepts value from user to search for
577```
578
579- Comments for individual lines appear above, or of the code to which
580 they refer.
581- The functional overview comment of a procedure may be indented one
582 space to aid readability.
583
584Example:
585
586```vba
587Public Sub DeleteCustomer(ByVal argintID As Long)
588 'Removes customer from Database
589 cnVideo.Execute "DELETE FROM Customer WHERE CustomerID=" & argintID
590End Sub
591```
592
593### Commenting code when doing maintenance work
594
595Avoid over-commenting code when doing maintenance work. Bear in mind
596the need to maintain overall clarity in the code, and remember that
597revision history should be taken care of by SourceSafe Make sure that
598any existing comments still make sense **after** you’ve made your
599changes - paying particular attention to any comments/explanations in
600the header of the routine. You are responsible for ensuring that
601**all** existing comments remain accurate (and that they still make
602sense) after your changes have been implemented. Although SourceSafe
603controls the history, It is handy to future users if new blocks of
604code are commented with the date, initials of developer and the CR
605number to aim future developers reading the code.
606
607### Etiquette when commenting code
608
609When you include one or more routines written by other developers in
610your project you should ensure that any author (and
611assumption/purpose) information in the routine header is kept
612accurate. You should probably retain the original author’s name, but
613you **must** also include your own name if you have changed it in any
614way at all.
615
616### Pre-compilation commands
617
618These are treated as a code IF statement would be. All code relating
619to the condition must be indented as if it was a normal IF block.
620These can be useful for including/excluding debug code etc. For
621example:
622
623```vba
624#Const DebugMode = True
625#IF DebugMode THEN
626 <code block>
627#ELSE
628 <code block>
629#ENDIF
630```
631
632## Error Handling
633
634### Generic error handler
635
636Consistent error handlers must be implemented. The following error
637handler should be used:
638
639```vba
640On Error GoTo ErrHandler
641 <code block>
642
643ExitHere:
644On Error Resume Next
645 <code block>
646Exit Sub
647ErrHandler:
648 [WriteErrLog Err.Number]
649 Select Case Err.Number
650 Case <Err No>
651 Resume Next
652 Case <Err No>
653 Resume ExitHere
654 Case Else
655 ' Unexpected Error
656 Resume ExitHere
657 End Select
658End Sub
659```
660
661### Error handling labels
662
663The labels **ErrHandler** and **ExitHere** are used both for
664consistency across routines, and to facilitate easier copying and
665pasting of error handlers between routines.
666
667## SQL Server stored procedures
668### Overview
669A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.
670
671In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.
672
673Take a look through each of these topics to learn how to get started with stored procedure development for SQL Server.
674
675You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these topics.
676### Commenting Code
677SQL Server offers two types of comments in a stored procedure; line comments and block comments. The following examples show you how to add comments using both techniques. Comments are displayed in green in a SQL Server query window.
678
679Line Comments
680To create line comments you just use two dashes "--" in front of the code you want to comment. You can comment out one or multiple lines with this technique.
681
682In this example the entire line is commented out.
683```
684-- this procedure gets a list of addresses based
685-- on the city value that is passed
686CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
687AS
688SELECT *
689FROM Person.Address
690WHERE City = @City
691GO
692```
693This next example shows you how to put the comment on the same line.
694```
695-- this procedure gets a list of addresses based on the city value that is passed
696CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
697AS
698SELECT *
699FROM Person.Address
700WHERE City = @City -- the @City parameter value will narrow the search criteria
701GO
702```
703Block Comments
704To create block comments the block is started with "/*" and ends with "*/". Anything within that block will be a comment section.
705```
706/*
707-this procedure gets a list of addresses based
708 on the city value that is passed
709-this procedure is used by the HR system
710*/
711CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
712AS
713SELECT *
714FROM Person.Address
715WHERE City = @City
716GO
717```
718Combining Line and Block Comments
719You can also use both types of comments within a stored procedure.
720```
721/*
722-this procedure gets a list of addresses based
723 on the city value that is passed
724-this procedure is used by the HR system
725*/
726CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
727AS
728SELECT *
729FROM Person.Address
730WHERE City = @City -- the @City parameter value will narrow the search criteria
731GO
732```
733### Naming conventions
734
735#### Naming Stored Procedure Action
736I liked to first give the action that the stored procedure takes and then give it a name representing the object it will affect.
737
738So based on the actions that you may take with a stored procedure, you may use:
739```
740Insert
741Delete
742Update
743Select
744Get
745Validate
746etc...
747```
748So here are a few examples:
749```
750uspInsertPerson
751uspGetPerson
752spValidatePerson
753SelectPerson
754etc...
755```
756Another option is to put the object name first and the action second, this way all of the stored procedures for an object will be together.
757```
758uspPersonInsert
759uspPersonDelete
760uspPersonGet
761etc...
762```
763Again, this does not really matter what action words that you use, but this will be helpful to classify the behavior characteristics.
764#### Naming Stored Procedure Object
765The last part of this is the object that you are working with. Some of these may be real objects like tables, but others may be business processes. Keep the names simple, but meaningful. As your database grows and you add more and more objects you will be glad that you created some standards.
766
767So some of these may be:
768```
769uspInsertPerson - insert a new person record
770uspGetAccountBalance - get the balance of an account
771uspGetOrderHistory - return list of orders
772```
773#### Schema Names
774Another thing to consider is the schema that you will use when saving the objects. A schema is the a collection of objects, so basically just a container. This is useful if you want to keep all utility like objects together or have some objects that are HR related, etc...
775
776This logical grouping will help you differentiate the objects further and allow you to focus on a group of objects.
777
778Here is a simple example to create a new schema called "HR" and giving authorization to this schema to "DBO".
779```
780CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]
781```
782#### Putting It All Together
783So you basically have four parts that you should consider when you come up with a naming convention:
784```
785Schema
786Prefix
787Action
788Object
789```
790Take the time to think through what makes the most sense and try to stick to your conventions.
791## **Database Coding Standard and Guideline**
792 ### Naming
793
794**Tables**: Rules: Pascal notation; end with an ‘s’
795- Examples: Products, Customers
796- Group related table names(1)
797
798**Stored Procs**: Rules: spAppName_GroupNameAction
799- Examples: spOrders_GetNewOrders, spProducts_UpdateProduct
800
801**Triggers**: Rules: TR_TableName_action
802- Examples: TR_Orders_UpdateProducts
803- Notes: The use of triggers is discouraged
804
805**Indexes**: Rules: IX_TableName_columns separated by "-"
806- Examples: IX_Products_ProductID
807
808**Primary Keys**: Rules: PK_TableName
809- Examples: PK_Products
810
811**Foreign Keys**: Rules: FK_TableName1_TableName
812- Example: FK_Products_Orderss
813
814**Defaults: Rules**: DF_TableName_ColumnName
815- Example: DF_Products_Quantity
816
817**Columns**: If a column references another table’s column, name it table nameID
818- Example: The Customers table has an ID column
819- The Orders table should have a CustomerID column
820
821**General Rules:**
822
823- Do not use spaces in the name of database objects
824- Do not use SQL keywords as the name of database objects
825- In cases where this is necessary, surround the object name with brackets, such as [Year]
826- Do not prefix stored procedures with ‘sp_’(2)
827- Prefix table names with the owner name (3)
828
829### Structure
830
831- Each table must have a primary key
832 - In most cases it should be an IDENTITY column named ID
833- Normalize data to third normal form
834 - Do not compromise on performance to reach third normal form. Sometimes, a little de-normalization results in better performance.
835- Do not use TEXT as a data type; use the maximum allowed characters of VARCHAR instead
836- In VARCHAR data columns, do not default to NULL; use an empty string instead
837- Columns with default values should not allow NULLs
838- As much as possible, create stored procedures on the same database as the main tables they will be accessing
839
840### Formatting
841
842- Use upper case for all SQL keywords
843 - SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
844- Indent code to improve readability
845- Comment code blocks that are not easily understandable
846 - Use single-line comment markers(?)
847 - Reserve multi-line comments (/*.. ..*/) for blocking out sections of code
848- Use single quote characters to delimit strings.
849 - Nest single quotes to express a single quote or apostrophe within a string
850 - For example, SET @sExample = ‘SQLâ€s Authority’
851- Use parentheses to increase readability
852 - WHERE (color=’red’ AND (size = 1 OR size = 2))
853- Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.
854- Use one blank line to separate code sections.
855- Use spaces so that expressions read like sentences.
856 - fillfactor = 25, not fillfactor=25
857- Format JOIN operations using indents
858 - Also, use ANSI Joins instead of old style joins (4)
859- Place SET statements before any executing code in the procedure.
860- Optimize queries using the tools provided by SQL Server(5)
861- Do not use SELECT *
862- Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server
863- Avoid unnecessary use of temporary tables
864 - Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better (6)
865- Avoid using <> as a comparison operator
866 - Use ID IN(1,3,4,5) instead of ID <> 2
867- Use SET NOCOUNT ON at the beginning of stored procedures (7)
868- Do not use cursors or application loops to do inserts (8)
869 - Instead, use INSERT INTO
870- Fully qualify tables and column names in JOINs
871- Fully qualify all stored procedure and table references in stored procedures.
872- Do not define default values for parameters.
873 - If a default is needed, the front end will supply the value.
874- Do not use the RECOMPILE option for stored procedures.
875- Place all DECLARE statements before any other code in the procedure.
876- Do not use column numbers in the ORDER BY clause.
877- Do not use GOTO.
878- Check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction if an error occurs
879 - Or use TRY/CATCH
880- Do basic validations in the front-end itself during data entry
881- Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server
882- Always use a column list in your INSERT statements.
883 - This helps avoid problems when the table structure changes (like adding or dropping a column).
884- Minimize the use of NULLs, as they often confuse front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form.
885 - Any expression that deals with NULL results in a NULL output.
886 - The ISNULL and COALESCE functions are helpful in dealing with NULL values.
887- Do not use the identitycol or rowguidcol.
888- Avoid the use of cross joins, if possible.
889- When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.
890- Avoid using TEXT or NTEXT datatypes for storing large textual data. (9)
891 - Use the maximum allowed characters of VARCHAR instead
892- Avoid dynamic SQL statements as much as possible. (10)
893- Access tables in the same order in your stored procedures and triggers consistently. (11)
894- Do not call functions repeatedly within your stored procedures, triggers, functions and batches. (12)
895- Default constraints must be defined at the column level.
896- Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword, as these results in an index scan, which defeats the purpose of an index.
897- Define all constraints, other than defaults, at the table level.
898- When a result set is not needed, use syntax that does not return a result set. (13)
899- Avoid rules, database level defaults that must be bound or user-defined data types. While these are legitimate database constructs, opt for constraints and column defaults to hold the database consistent for development and conversion coding.
900- Constraints that apply to more than one column must be defined at the table level.
901- Use the CHAR data type for a column only when the column is non-nullable. (14)
902- Do not use white space in identifiers.
903- The RETURN statement is meant for returning the execution status only, but not data.
904
905### # **Reference**:
9061) Group related table names:
907
908Products_USA
909Products_India
910Products_Mexico
911
9122) The prefix sp_ is reserved for system stored procedures that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. Time spent locating the stored procedure can be saved by avoiding the “sp_†prefix.
913
9143) This improves readability and avoids unnecessary confusion. Microsoft SQL Server Books Online states that qualifying table names with owner names helps in execution plan reuse, further boosting performance.
915
9164)
917False code:
918> SELECT *
919FROM Table1, Table2
920WHERE Table1.d = Table2.c
921
922True code:
923> SELECT *
924FROM Table1
925INNER JOIN Table2 ON Table1.d = Table2.c
926
9275) Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries do an “Index seek†instead of an “Index scan†or a “Table scan.†A table scan or an index scan is a highly undesirable and should be avoided where possible.
928
9296) Consider the following query to find the second highest offer price from the Items table:
930> SELECT MAX(Price)
931FROM Products
932WHERE ID IN
933(
934SELECT TOP 2 ID
935FROM Products
936ORDER BY Price DESC
937)
938
939The same query can be re-written using a derived table, as shown below, and it performs generally twice as fast as the above query:
940
941> SELECT MAX(Price)
942FROM
943(
944SELECT TOP 2 Price
945FROM Products
946ORDER BY Price DESC
947)
948
9497) This suppresses messages like ‘(1 row(s) affected)’ after executing INSERT, UPDATE, DELETE and SELECT statements. Performance is improved due to the reduction of network traffic.
950
9518) Try to avoid server side cursors as much as possible. Always stick to a ‘set-based approach’ instead of a ‘procedural approach’ for accessing and manipulating data. Cursors can often be avoided by using SELECT statements instead. If a cursor is unavoidable, use a WHILE loop instead. For a WHILE loop to replace a cursor, however, you need a column (primary key or unique key) to identify each row uniquely.
952
9539) You cannot directly write or update text data using the INSERT or UPDATE statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT. So, if you don’t have to store more than 8KB of text, use the CHAR(8000) or VARCHAR(8000) datatype instead.
954
95510) Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan at runtime. IF and CASE statements come in handy to avoid dynamic SQL.
956
95711) This helps to avoid deadlocks. Other things to keep in mind to avoid deadlocks are:
958
959Keep transactions as short as possible.
960Touch the minimum amount of data possible during a transaction.
961Never wait for user input in the middle of a transaction.
962Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed.
96312) You might need the length of a string variable in many places of your procedure, but don’t call the LEN function whenever it’s needed. Instead, call the LEN function once and store the result in a variable for later use.
964
96513)
966
967> IF EXISTS (
968 SELECT 1
969 FROM Products
970 WHERE ID = 50)
971
972Instead Of:
973
974> IF EXISTS (
975 SELECT COUNT(ID)
976 FROM Products
977 WHERE ID = 50)
978
97914) CHAR(100), when NULL, will consume 100 bytes, resulting in space wastage. Preferably, use VARCHAR(100) in this situation. Variable-length columns have very little processing overhead compared with fixed-length columns