Database Coding Standard and Guideline

Author's note: A surprising number of people still looking at this document. Very Cool! Thanks! There is a considerable amount of updated information available on this subject at www.bwunder.com There is also a bit of stuff below I never really agreed with. The point was to show an example of a coding standard that was created by the team. This is such an example. A long one at that. One of my goals for 2008 is to update the standard for SQL Server 2008 and eliminate all the stuff I do not agree with. The update will be posted on www.bwunder.com. Let me know what you'd like to see in there! (email). Also note that there are over 100 articles and many times that number of scripts for SQL Server posted on this site - ?bill

Summary

This document will provide a framework to aid in optimal usability of Microsoft SQL Server 7.0 schema, scripts and stored procedures developed for  applications by defining a reasonable, consistent and effective coding style. The qualities of usability as used here include readability by other database technicians, ease of implementation, maintainability, and consistency. The framework will serve to improve the application without unnecessary impact on development and unnecessary controls on personal coding preferences. For these reasons the framework will focus on identifier naming conventions that are intended to be used by all developers, general style guidelines indicating the preferred format and usage of SQL language components, and a definition of the database development methodology.

The identifier segment of the standard will formalize naming conventions. All schema, scripts and stored procedures will conform to all elements of this area of the document.

The general guideline will include SQL statement formatting and outlines for solutions to more complex components within scripts and stored procedures. This instrument is intended as a ‘best practice’ model consistent with the identified architecture and requirements. It may be necessary to adapt the guideline to specific solutions within the application. While the guidelines are not mandatory, adherence will aid in the ultimate success of the application and ease of maintenance. All developers will be expected to reasonably defend any variance from the guideline.

The combination of conformance to standards and development within the stated guidelines will be measured and assessed in the context of the methodology. The methodology will present structure and consistency through clearly defined requirement specifications, change control procedures, code review, testing, controlled iterative development cycles, and regular developer evaluations.

There is a perceptive risk in not defining a ubiquitous and mandatory coding standard. Stored procedure and script development may become sloppy and unreadable resulting in diminished usability. In actuality, this is a deficiency in the developer and/or the development methodology not the framework. Without the controls of testing, review and feedback to support a high quality development team any standard will not assure usability. Defining most issues of style and layout within the context of guidelines rather than dogma frees the developer to produce robust and creative solutions. This latitude in the framework is intended to balance mechanics and creativity. Clear definition and execution of the controls will assure the purpose of this or any coding standard.

 


Identifiers

Object type

Prefix

Example

Primary key Clustered

pkc_

pkc_MY_TABLE__Column

Primary key Nonclustered

pkn_

pkn_TB_TABLE__Column_List

Index Clustered

ixc_

ixc_TS2_TABLE__Column

Index Nonclustered

ixn_

ixn_TB_TABLE__Column_List

Foreign key

fk_

fk_THIS_TABLE__ColumnB__to__TB_PKEY_TABLE__ColumnA

Unique Constraint

unq_

unq_TB_TABLE__Column_List

Check Constraint

chk_

chk_TB_TABLE__Column

Column Default

dft_

dft_TB_TABLE_Column_List

Passed Parameter

@p

@pPassedVariableName

Local Variable

@

@VariableName

Table

TB_, *_

TB_TABLE_NAME (see detail below)

View

VW_

VW_NET_ACTIVE_UNITS

User Defined Scalar Function

ufs_

ufs_return_value_name

User Defined Table Function

uft_

uft_TB_TABLE_NAME

Stored Procedure

*

Eds_Def (see detail below)

Script type

Prefix

Example

Stored procedure script

proc_

proc_Calendar.sql

Schema script

def_

def_Calender.sql

Conversion script

conv_

conv_Schedule.sql

Rollback script

rbk_

rbk_Schedule.sql

Object Type

Suffix

Example

Account

_Acct

Process_Corp_Acct

Address

_Addr

Contact_Addr

Amount

_Amt

Total_Credit_Amt

Balance

_Bal

Available_Bal

Date or Datetime

_Dt

Active_Dt, @Archive_Dt

Description

_Desc

Product_Desc

Date of Birth

_DOB

Alternate_DOB

Indicator

_Ind

Net_Gross_Ind

Line(n)

_Ln(n)

Address_Ln2, @pOrderLn

Number

_Nbr

Bank_Nbr

Record identifier/identity

_Id

Entity_Id, @pEntityId

Tax Id Number

_TIN

Merchant_TIN

Card Transactions

_Trans

Daily_Nbr_Trans

Zip Code

_Zip

Recipient_Zip

Name, Type, Flag, etc.

 

Tables

create table {database name}.{table owner}.{table name}

       ([{table name}_Id {data type} [identity(begin, step),]]

       {column name} {data type} {null | not null},

       {column name} {data type}  {null | not null}

              constraint dft_{table name}__{column name}

                     default ({default value}),

       {column name} {data type}  {null | not null}[,

       constraint pk{u | n}_{table name}__{pkey column}

              Primary key {clustered | non-clustered} ({pkey column})][,

constraint fk_{this table}__{column [list]}__to__{other table}__{column [list]}

foreign key {column [list]}

references {other table}({column [list]})][,

constraint unq_{table name}__{column [list]}

unique key ({column [list]}][,

constraint chk_{table name}__{column [list]}

       check ({check expression}),])

 

Example:

create table School.dbo.TB_COURSE

(Course_Id int identity(1, 1) not null,

Educator_Id int not null,

School_Id int not null,

Course_Name varchar(50) not null,

Start_Date datetime

Constraint dft_TB_COURSE__Start_Date

default (getdate()),

End_Date datetime null,
constraint pkc_TB_COURSE__Course_Id

primary key clustered (Course_Id),

            constraint

fk_TB_COURSE__Educator_Id__to__TB_EDUCATOR__User_Id

foreign key (Educator_Id)

references TB_EDUCATOR(User_Id),

constraint

fk_TB_COURSE__Course_Id__to__TB_SCHOOL__School_Id

foreign key (Course_Id)

references TB_SCHOOL(School_Id),

constraint chk_TB_COURSE__Start_Date__End_Date

check (Start_Date <= End_Date))

TSYS_REC_30, EDS_REC_40, PVS_ACCOUNT, etc.

TB_CALLING_CARD not TB_CALLING_CARDS

fk_TB_COURSE__Educator_Id__to__TB_USER__Id

If (objectProperty(object_id('{constraint name}'),

'IsConstraint') is not null)

alter table {table name}

drop constraint {constraint name}

If (objectProperty(object_id('{constraint name}'),

'IsConstraint') is null)

alter table {fully qualified table name}

add constraint {constraint name}

default {constraint value}

[for {column name}]    

 

If (ColumnProperty(object_id('{table name}')

,{column name},

'AllowsNull') is not null)

alter table {fully qualified table name}

drop {column name}

If (ColumnProperty(object_id('{table name}')

,{column name},

'AllowsNull') is null)

alter table {fully qualified table name}

      add {column name} {data type} {null | not null}

[constraint {default name}

default ({default value})]

If (ColumnProperty(object_id('{table name}')

,{column name},

'AllowsNull') is not null)

alter table {fully qualified table name} [with nocheck]

alter column {column name} {new compatible data type}

 

If (columnProperty(object_id('{table name}’),

{column name},

'AllowsNull') is not null)

alter table Plans.dbo.TB_DESIGNS

drop column Color_Id

 

                        or

If (objectProperty(object_id

('fk_TB_EDUCATOR_SCHOOL__Educator_Id__to__TB_EDUCATOR__User_Id'),

'IsConstraint') is not null)

alter table {table name}

drop constraint

fk_TB_EDUCATOR_SCHOOL__Educator_Id__to__TB_EDUCATOR__User_Id

 

If (objectProperty(object_id

('fk_TB_EDUCATOR_SCHOOL__Educator_Id__to__TB_EDUCATOR__User_Id'),

'IsConstraint') is null)

alter table District.dbo.TB_EDUCATOR_SCHOOL

add constraint

fk_TB_EDUCATOR_SCHOOL__Educator_Id__to__TB_EDUCATOR__User_Id

foreign key (Educator_Id)

references TB_EDUCATOR (User_Id)

Indexes

create {clustered | nonclustered} index {index name}

on {fully qualified table name}

({column list})

{options}

Example:

create nonclustered index ixn_TB_TICKET__Expire_Dt

on Events.dbo.TB_EVENT(Expire_Dt)

ix{c | n}_{table name}__{column name}[__{column_name}[..]]

Examples:

ixn_TB_DISTRIBUTOR_Name

ixc_TB_ACTIVITY__Itinirary_Id__Active_Dt

ixn_TB_COURSE__Instructor_Id

Stored Procedures (and other dml scripts)

use {database name}

if (objectProperty(object_id('{owner}.{procedure name}'),

‘IsPRocedure') is not null)

      drop procedure {owner}.{procedure name}

 

GO

 

create procedure {owner}.{procedure name}

   [{parameter}  {data type}][,

…]

as

/*******************************************************************

* PROCEDURE: {procedure name}

* PURPOSE: {brief procedure description}

* NOTES: {special set up or requirements, etc.}

* CREATED:  {developer name} {date}

* MODIFIED

* DATE            AUTHOR                  DESCRIPTION

*-------------------------------------------------------------------

* {date}          {developer} {brief modification description}

*******************************************************************/

[declare {variable name} {data type}[,

…]]

 

[{set session}]

 

[{initialize variables}]

 

{body of procedure}

 

return

 

{error handler}

 

Example:

 

use Subscriptions

if (objectProperty(object_id('dbo.UnsubscribeMagazine'),

‘IsPRocedure') is not null)

      drop procedure dbo.UnsubscribeMagazine

 

GO

 

create procedure dbo.UnsubscribeMagazine

@pMagazineId int,

@pUserId int
as

set xact_abort on

 

delete Subscriptions.dbo.TB_MAILDROP
where Magazine_Id = @pMagazineId

and User_Id = @pUserId

 

delete Magazines.dbo.TB_SUBSCRIBERS

where Magazine_Id = @pMagazineId

and User_Id = @pUserId

 

return

 

 

 

 

 

 

 

 

 

Northwind.dbo.TB_ORDER

master.dbo.sysdatabases

 

 

 

Declare @iRowCount int,

      @iError

Select Id from MyDB.dbo.MyTable

Select @iRowcount = @@rowcount,

      @iError = @@error

if @iError <> 0

      goto MyErrorHandler

if @iRowCount > 0

      delete MyDB.dbo.MyTable

Not

select Id from MyDB.dbo.MyTable

if @@error <> 0

      goto MyErrorHandler

if @@rowcount > 0 -- rowcount will be about if @@error

      delete MyDB.dbo.MyTable

o        Reserved words (begin, end, table, create, index, go, identity).

 

o        Built-in types (char, int, varchar).

 

o        System functions and stored procedures (cast, select, convert).

 

o        System and custom extended stored procedures (xp_cmdshell).

 

o        System and local variables (@@error, @@identity, @value).

 

o        References to system table names (syscolumns).

 

o        Table alias name within a SQL statement.

Error handling

{fully qualified procedure name} : {message}

Example

MyDatabase.dbo.MyStoredPorcedure : A strange error has occurred?

sp_addmessage     msg_id,
                  severity
,
                  {message text}[,

{language}[,
      'with_log'[,
      'replace']]]

 

beginning of each procedure that might raise the error:

 

declare @sProcedureName varchar(255)

select @sProcedureName = db_name()

+ '.'

+ user_name(objectproperty(@@procid,'OwnerId')) + '.'

+ object_name(@@procid)

Schema Scripts

Database Security

sp_grantlogin ‘MyDomain\SomeUser’,’

exec sp_grantdbaccess ‘MyDomain\SomeUser’

use MyDB

exec sp_addrolemember   ‘AppropriateRole’,

‘MyDomain\SomeUser’

use MyDB

grant exec on MyProcedure to ‘ApproptriateRole’

Formating

set @sExample = ‘Bill’’s example’

if ((select 1 where 1 = 2) is not null)

Whitespace

Comments

 DML Statements (select, insert, update, delete)

PUBS.dbo.TitleAuthor

master.dbo.sysdatabases

select c.Name, a.Description

from User.dbo.TB_ADDRESS a

inner join VIOLATIONS.dbo.TB_INCIDENT i

On a.Id = i.Address_Id

=, >, <, <>, in, exists, not, like, is null, and, or

 

If exists(select 1

from EQUIPMENT.dbo.TB_LOCATION

where Type = 50)

rather than,

if ((select count(Id)

from EQUIPMENT.dbo.TB_LOCATION

where Type = 50) > 0)

 Select

(@pointsReceived / @pTotalPoints) as Average

select @@identity as UserId

is preferred over

select @@identity as Id

 {[alias.]column name}[,

 …]]

from {database name}.{object owner}.{table name} [[{alias 1}]

[inner join {database name}.{owner name}.{table name} [{alias 2}]

on {alias 1}.{column name} = {alias 2}.{column name}[

{next join}]]

[where {constraint condition}

[and {constraint condition}

[…]]]
[group by {column [list]}

[having {constraint condition}]]
[order by {column [list]}]

[{union}
{next select statement}]

Example:


select      t.Task_Id,

t.Course_Id,

t.Due_Dt,

t.Start_Time,

t.End_Time,

t.Name,

et.Completed_Flag,

et.Completed_Dt

from BusyWork.dbo.TB_TASK t

inner join BusyWork.dbo.ENROLLMENTTASK et

on t.Task_Id = et.Task_Id
where t.Due_Dt >= @pStartDate

and t.Due_Dt <= @pEndDate

and et.Member_Id = @pMemberId
order by    t.Due_Dt,

t.Start_Time 

inner join Task.dbo.ENROLLMENT et

on t.TASK_ID = et.TASK_ID
where et.MEMBER_ID = @pMemberId
and ((t.DUE_DT <= @pStartDate)

or (t.DUE_DT >= @pEndDate)

or (et.COMPLETED_FLAG = 1))

Inserts

insert SUBSCRIBE execute SUBSCRIBERS_BUILDNEW_SYSTEM

({column name}[,

{column name}[,

…}})

values

      ({value or variable}[,  --{comment hard coded value}

      {value or variable}[,   --{comment hard coded value}

         …]]                          

Example:

insert Parts.dbo.TB_TOASTER

(TOASTER_ID,

MANUFACTURER_ID,

NAME,

NOTES)

values      (1,                           -- example only 

1,                            -- example only

'spring',                     -- example only

'cross sell handle latch')    –- example only

 

({column name}[,

{column name}[,

…}})

select      [{target column name =}]({column name}[,

[{target column name =}]{column name}[,

…}})

{from clause

[{where clause}]}

Example:

insert into Parts.dbo.TB_TRACTOR

(Tractor_Id,

Manufacturer_Id,

Name)

`     select      Id,

@sBoltId,

‘plow bolts’      -- name from vendor catalog

from Equipment.dbo.TB_HEAVYDUTY
where Id = @pTractorId

 

·         Provide an inline comment to explain any hardcoded value.

Updates

set   {column} = {expression}[,

{column} = {expression}[,

…]]

{where clause}

Example:

update Articles.dbo.TB_STATISTICS

set  READ_HITS = READ_HITS + 1,

LAST_READ_DT = current_timestamp

where ARTICLE_ID = @pArticleId

set   {column} = {expression}[,

{column} = {expression}[,

…]]

{from clause}

[{where clause}]

Example:

update PUBS.dbo.TB_TITLES

set Total_Sales = t.Total_Sales + s.Quantity
from Pubs.dbo.TB_TITLES t

inner join Pubs.dbo.TB_SALES s
on t.Title_Id = s.Title_Id

Deletes

Example

delete from WebLog.dbo.TB_ARTICLE_STATISTICS
where ARTICLE_ID = @pArticleId

Example:

delete WebLog.dbo.TB_ARTICLE_STATISTICS as
where exists (select ID

from ARTICLES.dbo.TB_EXPIRED

where ARTICLE_ID = as.ARTICLE_ID)

Transactions

set xact_abort on

to manage a connection.

Begin Transaction [{transaction name}]

{statements}

If {error}

Commit Transaction  [{transaction name}]

else

Rollback Transaction  [{transaction name}]

Transact-SQL Flow-of-control statements

{statement}
else

if ({condition})

{statement}
else

{statement}

or

if ({condition})

begin

{statement}

.

.

.

{statement}
end

else

{statement}

 

{statement}

            or


while ({condition})

begin

{statement}

.

.

.

{statement}
end

select case [{column or variable}]

when {value | expression} then {result if this value}

[when {value | expression} then {result if this value}]

[else {default result}]]

end

EXAMPLE:

select u.ID,

case u.TYPE

when 10 then eu.EMAIL

when 20 then pu.EMAIL

else ‘none provided’

end

from WebSite.dbo.Users u

inner join Users.dbo.TB_ELECTRICIAN_USER eu

inner join Users.dbo.TB_PARENT_USER pu

where u.DISPLAY_NAME = ‘Smith, Bob’

Cursors

Linked Servers 

Use the following outline when creating linked server:

/*

Template for use in creating an aliased SQL Server 7.0 linked server. The same procedure can be used for all lifecyle environments once built from this template. User executing this procedure must be connected as sa to create a linked server and must provide the sa password of the remote system to create the objects needed on the remote server to achieve the link. The local user that will access the link - provided as the parameter aliasUser when calling the procedure - must be an NT domain account. Only users connected to the local SQL Server as this account will be able to access the link. In order to be connected to the SQL Server as an NT domain account, the user must be logged into the domain as an interactive workstation user or an NT service.

The domain account cannot be impersonated nor specified in a connection string. The remote user that will act as a proxy account on the remote server will be a SQL Server login and user, not an NT domain account. All permissions needed on the linked server must be explicitly added to this procedure. If this SQL Server login already exists on the remote server, all pre-existing permissions in all databases on that server will be lost when this procedure is executed. The only place this user's password will be stored is in the linked server connection on the local server in encrypted form. This user should not be used in any other context to avoid a compromised security condition.

 

replace the literal <linked server alias name> with the name to be used in the server position of all 4 part

qualified calls to the remote server (server.database.owner.object) -- literal occurs 5 times in this script

 

replace the literal <target db on linked server> with the database to be accessed on the remote server.

-- literal occurs once in this script

 

-- stored procedure call prototype

exec lnk<linked server alias name> '<NT domain account local user>',

                                   '<remote SQL Server to be linked>',

                                   '<remote SQL Server sa password>',

                                   '<remote SQL Server user>',

                                   '<remote SQL Server user password>'

*/

 

use admin

go

if (objectproperty(object_id('lnk<linked server alias name>'),'IsProcedure') = 1)

        drop procedure lnk<linked server alias name>

go

 

create procedure lnk<linked server alias name>

        @aliasUser varchar(30),

        @targetServer varchar(30),

        @targetSaPassword varchar(30),

        @sqlUser varchar(30),

        @sqlPassword varchar(30)

as

-- must be sa when you run this script

-- must modify osql call to grant all needed permissions on remote side

-- uses a SQL login on remote side, and an NT login on local machine

-- all jobs that use the linke alias must be running under the NT login

declare @returnCode int,

        @linkAlias varchar(30),

        @targetDb varchar(30),

        @cmd varchar(8000)

 

set nocount on

 

-- the alias name will be the servername used in

-- all 4 part calls to remote machine

select @linkAlias = '<linked server alias name>'

-- remote calls will only have access to this database

set @targetDb = '<target db on linked server>'

 

-- add the NT logon as a local login if it is not already a local login

if (select suser_id(@aliasUser)) is null

        exec master.dbo.sp_grantlogin @aliasUser

 

-- add the remote SQL user to the target server and grant needed

-- permissions here

set @cmd = 'if (select suser_id(''' + @sqlUser + ''')) is null  exec sp_addlogin '

         + @sqlUser + ', ' + @sqlPassword + ', ' + @targetDb

         + ' if (select user_id(''' + @sqlUser + ''')) is null exec sp_grantdbaccess ' + @sqlUser

 

-- explicitly grant all permissions the reomote user needs on the

-- remote server here

 

-- table permissions - example:

-- + ' grant insert, update on account to ' + @sqlUser

+ ' grant <any of select, insert, update, delete> on <table> to '

+ @sqlUser

-- stored procedure - example:

-- + ' grant exec on byroyalty to ' + @sqlUser

+ ' grant exec on <procedure> to ' + @sqlUser

 

-- add user and permissions to remote server via osql

set @cmd = 'osql -S' + @targetServer + ' -Usa -P' + @TargetSaPassword + 

           ' -d' + @targetDb + ' -Q"' + @cmd +'"'

exec master.dbo.xp_cmdshell @cmd

 

-- now that the remote user exists, create the aliased linked server

-- drop the linked server and recreate if it already exists

exec @returnCode = master.dbo.sp_helpserver @linkAlias

if (@returnCode = 0)

        begin

                exec master.dbo.sp_droplinkedsrvlogin @linkAlias,

@aliasUser

                exec master.dbo.sp_droplinkedsrvlogin @linkAlias, NULL

                exec master.dbo.sp_dropserver @linkAlias             

        end

EXEC master.dbo.sp_addlinkedserver

        @server = @linkAlias,

        @srvproduct = '',

        @provider = 'SQLOLEDB',

        @datasrc = @targetServer,

        @catalog = @targetDb

 

EXEC master.dbo.sp_addlinkedsrvlogin

        @rmtsrvname = @linkAlias,

        @useself = 'false',

        @locallogin = @AliasUser,

        @rmtuser = @sqlUser,

        @rmtPassword = @sqlPassword

 

return

Sending Email Notifications

When it is necessary to originate an administrative email message within a stored procedure, always reference an address associated to a sysoperators member. This will enable the same code to used in development and testing without sending misleading messages to production personnel because the same operator can be configures to have a different email address in different software lifecycle environments. Members are added to sysoperators through the stored procedure sp_addoperator (see Books on Line) or through the Enterprise Manager.

To send mail, it is necessary to query the sysoperators table in msdb

Example:

declare     @EmailTo                varchar(100),

            @EmailSubject           varchar(50),

            @EmailMessage           varchar(1000)

 

select card_id where unit is null

if @@rowcount > 0

      begin

select @EmailTo = email_address

from msdb.dbo.sysoperators

where name = 'PRODUCTION OPERATIONS'

      select @EmailSubject = 'EDS Cards Missing Units.'

            select @EmailMessage = 'Cards are missing units'

      exec master.dbo.xp_sendmail   @recipients = @EmailTo ,

@message = @EmailMessage,

@subject = @EmailSubject

      end

  Methodology

Development Environments

The development environments will consist of Unit Test, System Test – also commonly know as development or application integration, QA, and Production segments.

Each database developer will have SQL Server Desktop Edition and SourceSafe client application installed on the desktop. The desktop will serve as the Unit Test platform for all schema, stored procedure and script development, testing, and debugging.

The network login share (normally mapped to H:) is specified as the SourceSafe Current Working Directory. This location is chosen because it is backed up to tape periodically. This means that objects that are checked out as part of a development project will enjoy a closer approximation to the level of security as objects checked into SourceSafe. Checking in to SourceSafe is one step better as it assures a local copy of all objects and a copy stored in the SourceSafe repository and has no dependency on an uncoordinated backup operation.

System test is intended as a front end/middle ware/application server development tool. No database changes should be introduced from Unit Test to the Integration environment without moving through the change control system. If revisions or additional changes are needed, the source from the change control system must be checked out and the revisions completed and tested in the Unit Test environment. This will provide a greater level of stability for software developers than a chaotic environment where schema might change unbeknownst to an application developer in the middle of a test cycle.

Changes will be introduced to environments beyond the Integration system only via tested scripts and within the context of the company wide change control policy and procedures.

Class Model

Change Control

All SQL Server DML, DDL creation and changes will be scripted and the scripts will be the vehicle to introduce additions or changes to the Integration environment and beyond. Changes may be implemented in the Unit Test environment using GUI tools or ad hoc techniques at the pleasure of the developer. Such changes will need to be scripted and the scripts tested before migrating them to the Integration environment. For this reason, it will generally be more efficient to always script changes and avoid EM or other non scripted techniques.

A consistent configuration and strategy for moving objects through the change control system will assure clean and consistent versioning of the database schema and scripts. The details of the  system are presented in the document, “SourceSafe Database implementation Details” A general overview of the change control steps are:

All related objects should be kept checked out until all objectives are met for the development project. This will avoid conflicts created where incomplete development projects are included in release version.

In an effort to reduce errors, omissions, and surprises when changes are implemented into a  production environment the  Database Team will adhere to the following procedure at all times. These procedures will not only help protect The organization from the hazards of missed manual steps, but enable any member on the database team to provide support for any change and help us as we work to establish a standard for our SourceSafe implementation. This procedure has the endorsement of QA and Production Operations.

1. All database changes will have a fully endorsed and properly numbered PCR (Production Change Request - Pi087) before the change is introduced to production. No Exceptions.

2. All database changes included in a PCR will be introduced via a single script with a .sql extension. The script will be tested before the change is delivered to QA. The script will be the method of delivery of the change to the designated QA server and then to all Production servers. This script will be stored in the  SourceSafe repository -- share name \\Barney\VSS_PROJECT -- at the project path $/PROJECT/Database/QA/<server name>/Conversions/<good description>.sql at the time the project is delivered to QA and then also added to the same sourceSafe repository at the project path $/PROJECT/Database/Production/<server name>/Conversions/<PCR number>/<PCR number>.sql at the time it is delivered to production. The PCR number will be added to the "comments" SourceSafe attribute of the QA script once it is known.

3. A rollback procedure for all database changes will be outlined on the PCR and will be available and fully tested as a single script named as the PCR with an extension of .rbk when the change is delivered to QA. It will then be the option of QA to test the rollback procedure as deemed appropriate. This script will be stored in the  SourceSafe repository -- share name \\Barney\VSS_PROJECT -- at the project path $/PROJECT/Database/Production/<server name>/Conversions/<same description as script in 2 above>.rbk at the time the project is delivered to QA and then also added to the same sourceSafe repository at the project path $/PROJECT/Database/Production/<server name>/Conversions/<PCR number>/<PCR number>.rbk at the time it is delivered to each Production Server. The PCR number will be added to the "comments" SourceSafe attribute of the QA script once it is known.

4. All database changes will include documentation as to recoverability. In general, this will mean that a Word document covering the steps needed to reimplement the changes should a new server need to be built or an existing server be lost or other considered scenarios where the changes need to be recreated will be produced and placed in the above mentioned SourceSafe Project and any necessary scripts for this recovery process will be added or modified in support of the document.

5. All database changes will be verified by a second member of the database team and/or subjected to the database team's code review process prior to delivery to QA.

Unit Test Plans

All scripts and stored procedures must be tested before leaving the Unit test environment. A test plan is created or modified as needed and placed in the SourceSafe repository. The objective of the test plan is to document the procedure used to assure that every line of code does what it is supposed to do only when it is supposed to complete its instruction. There is a template for test plans at the test plans root of the Database SourceSafe hierarchy. Any notes, observations, or deficiencies that should be recorded in the test plan.

The test document will define:

·         The state of the database at the onset of the test cycle.

The default state will be that the database(s) will be freshly rebuilt and populated using the ‘populate scripts’ with an satisfactory population level. Any other setup or configuration should be noted on the test document.

·         All expected exit conditions within the script or procedure.

Every line of code in the script or procedure must be executed in the test cycle. If revision is necessary, the test cycle should be restarted after all changes are made.

·         Each test required in the test cycle with expected result.

·         An explanation of any variance from the expected result.

·         The time test cycle completed and testers initial.

Code Review

The database team will conduct regular code reviews of all stored procedures and scripts. Code reviews are intended to improve the quality of the applications and as a learning experience for all database developers. The code review will not be a tool used to evaluate any person, rather to focus on technical consideration of code. The review will not cover how a solution should be coded, rather it will examine the code to identify defects in coding standard compliance, logic, performance, portability, audit-ability, error handling, architectural compatibility, and security. Everyone that writes SQL that accesses a  database is expected to participate in the review process.

Code reviews will not have a static format, but will change as is deemed appropriate by the team. Initially, the format will be loosely based upon the “Inspection Review” described in Code Complete by Steve McConnell (Microsoft Press, 1993). This is a role-based process. The main role going in to a review is the Author. This person must identify and prepare about 10 printed pages of code that will be reviewed. The Author will print a copy of the code and present it to each reviewer 24 hours before the review is scheduled. The print-out is to be in landscape format in a 10 or 12 pitch fixed width font with numbered lines and numbered pages. The lead role during the code review is the Moderator. This person must keep the review meeting on topic and on time. The Moderator will also lead a review of identified issues and assure that all tasks are assigned as identified in the review. At the conclusion of each meeting, the Moderator is responsible for assigning roles for the next review meeting. A second role during the review is that of the Scribe. The scribe will make note of identified defects and subsequent task assignments. After the review meeting, the scribe will prepare an email documenting noting the task assignments and deliver to all persons involved in the review as well as direct line management. A final role, that of Reviewer, will require preparation time before the review and active participation during the review. Reviewers should spend up to 90 minutes prior to the review examining the code. All defects should be identified at the start of the review meeting. The purpose of the review is simply to discuss and reach a consensus agreement on which identified issues are indeed defects. Roles will be rotated among team members. It may be necessary for a person to assume multiple roles. For example, the Moderator and Scribe may be Reviewers and the Moderator may be the Scribe, or the Author may be the Scribe.

Time requirement estimates for each role for one review:

            Moderator          2-3 hours

            Scribe               2 hours

            Author               2 hours

            Reviewer           2-3 hours

 

 Evaluation

It is important for personal growth and for the efficacy of the team that all members are kept informed of their performance. An approved employee evaluation format that will be followed Issues, concerns and accolades around the coding standard will be included in the evaluation process for all database team members.

Development Process