database coding standard and guideline

8/7/2019 Database Coding Standard and Guideline

1/23

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 subjectatwww.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. Letme know what you'd like to see in there! (email). Also note that there are over 100 articles and

many times that number ofscripts 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 andeffective coding style. The qualities of usability as used here include readability by other databasetechnicians, ease of implementation, maintainability, and consistency. The framework will serve to

improve the application without unnecessary impact on development and unnecessary controls onpersonal coding preferences. For these reasons the framework will focus on identifier namingconventions that are intended to be used by all developers, general style guidelines indicating thepreferred format and usage of SQL language components, and a definition of the database developmentmethodology.

The identifier segment of the standard will formalize naming conventions. All schema, scripts and storedprocedures 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 complexcomponents within scripts and stored procedures. This instrument is intended as a best practice modelconsistent with the identified architecture and requirements. It may be necessary to adapt the guideline tospecific 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 toreasonably defend any variance from the guideline.

The combination of conformance to standards and development within the stated guidelines will bemeasured and assessed in the context of the methodology. The methodology will present structure andconsistency 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 procedureand 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 thecontrols of testing, review and feedback to support a high quality development team any standard will notassure usability. Defining most issues of style and layout within the context of guidelines rather thandogma frees the developer to produce robust and creative solutions. This latitude in the framework isintended to balance mechanics and creativity. Clear definition and execution of the controls will assurethe purpose of this or any coding standard.


2/23

Identifiers

y Caseo Use all upper case for table and view nameso Use mixed case for column names and variableso Use mixed case for stored procedure name

o Use lower case for other names except use the same case as indicated above where atable or column is used in another objects name

y Prefixes and suffixes:o Use the following standard prefixes for database objects:

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)o Use the following standard prefixes for scripts:

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

o Save all scripts using the .sqlextensiono Use the following standard column and variable suffix abbreviations:

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


3/23

Date ofBirth _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

o All other suffixes should be whole words

Name, Type, Flag, etc.

y Stored procedure names ought to reflect the name of the primary data source, the action theprocedure accomplishes, and the audience that uses the procedure.

o Picking a table name can be a black art, especially when the procedure spans multiple

tables. Generally, the best table name is the one that represents most of the data or theprimary join table. Murphy's Law as applied to procedure names: The longest, mostinconvenient procedure name is usually the "right" one.

o The audience might typically be one of the following: Import Export Custom Operations Report

System (not directly available to the front end)o Avoid abbreviations where possible though acronyms that have meaning to the business

are acceptable.

Tables

y Use the following outline for creating 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,


4/23

Educator_Id int not null,School_Id int not null,Course_Name varchar(50) not null,Start_Date datetime

Constraint dft_TB_COURSE__Start_Datedefault (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_Idforeign key (Educator_Id)references TB_EDUCATOR(User_Id),

constraintfk_TB_COURSE__Course_Id__to__TB_SCHOOL__School_Idforeign key (Course_Id)references TB_SCHOOL(School_Id),

constraint chk_TB_COURSE__Start_Date__End_Datecheck (Start_Date


5/23

o Unique constraints are preferred over unique indexes for performance reasons, though ifa tables data is accessed by the non primary key unique column(s) a unique index maybe appropriate.

y Altering Tableso alter table should be used in scripts that will be used for upgrading existing table, not

when creating tables.

o Use the following outline to drop an existing column or table constraintIf (objectProperty(object_id('{constraint name}'),

'IsConstraint') is not null)alter table {table name}

drop constraint {constraint name}o Use the following outline for adding or changing an existing column or table constraint.

(Just drop it first if your changing it.)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}]

o Use the following outline to drop an existing column.If (ColumnProperty(object_id('{table name}')

,{column name},'AllowsNull') is not null)

alter table {fully qualified table name}drop {column name}

o Use the following outline for adding a column.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})]

o Use the following outline for altering a default on an existing column. (Note that It may benecessary to remove foreign keys before altering a column and in a few cases it may benecessary to duplicate the table with the desired new structure and move the data to thenew table. This latter operation can be scripted using the EM GUI in a unit testenvironment rather than coding the entire operation by hand.)

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}

o Alter table examples:If (columnProperty(object_id('{table name}),

{column name},'AllowsNull') is not null)

alter table Plans.dbo.TB_DESIGNSdrop column Color_Id


6/23

orIf (objectProperty(object_id('fk_TB_EDUCATOR_SCHOOL__Educator_Id__to__TB_EDUCATOR__User_Id'),

'IsConstraint') is not null)alter table {table name}drop constraintfk_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 constraintfk_TB_EDUCATOR_SCHOOL__Educator_Id__to__TB_EDUCATOR__User_Id

foreign key (Educator_Id)references TB_EDUCATOR (User_Id)

Indexes

y Use the following outline for creating Indexescreate {clustered | nonclustered} index {index name}

on {fully qualified table name}({column list}){options}

Example:

create nonclustered index ixn_TB_TICKET__Expire_Dton Events.dbo.TB_EVENT(Expire_Dt)

o Explicitly name all indexes and include the table name and all indexed columns in indexorder.

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

y Unless there is a documented benefit, do not specify a fill factor when creating an index.

Stored Procedures (and otherdmlscripts)

y Use the following outline for creating stored proceduresuse {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


7/23

/******************************************************************* * 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 Subscriptionsif (objectProperty(object_id('dbo.UnsubscribeMagazine'),

IsPRocedure') is not null)drop procedure dbo.UnsubscribeMagazine

GO

create procedure dbo.UnsubscribeMagazine@pMagazineId int,@pUserId int

asset xact_abort on

delete Subscriptions.dbo.TB_MAILDROPwhere Magazine_Id = @pMagazineIdand User_Id = @pUserId

delete Magazines.dbo.TB_SUBSCRIBERSwhere Magazine_Id = @pMagazineIdand User_Id = @pUserId

return

y Always specify the owner when creating a procedure scripty Do not use alter procedure. Scripts should be structured so that procedures are dropped and

recreated.y Do not use temporary stored procedures.

y Do not define default values for parameters. If a default is needed, the front end will supply thevalue.


8/23

y Do not use output parameters. Any information returned to the client should be done via a resultset.

y Do not create stored procedures that return multiple result sets.

y Do not use the encryption option except as other wise noted above.

y Never use the recompile option.

y Fully qualify all stored procedure and table references in all stored procedures.

y Place all declare statements before any other code in the procedure to give the query optimizerthe best shot at reusing query plans.

y Place set statements before any executing code in the procedure.y Fully qualify all table and view references with the database name and the table owner name.Northwind.dbo.TB_ORDERmaster.dbo.sysdatabases

y Avoid calling stored procedures from stored procedures because error handling is very

unpredictable in such circumstances. I procedures must be nested, fully qualify the procedurename.y Fully qualify all system stored procedures used in a stored procedure. This will optimize

performance as the system will never have to search for the system procedure.y Enumerate all column lists. Do not use the * wildcard.

y In order to capture two or more SQL Server global variables pertaining to the same statement,declare a variable for each global to be identified and assign all in a single select immediatelyafter the statement. Capturing more than one global variable one at a time will produce erroneousresults.

Declare @iRowCount int,@iError

Select Idfrom MyDB.dbo.MyTableSelect @iRowcount = @@rowcount,

@iError = @@errorif@iError 0

goto MyErrorHandlerif@iRowCount > 0

delete MyDB.dbo.MyTableNotselect Idfrom MyDB.dbo.MyTableif@@error 0

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

delete MyDB.dbo.MyTable

y Use all lower case for system names, statements, variables, and functions: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).


9/23

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

o References to system table names (syscolumns).

o Table alias name within a SQL statement.

Errorhandling

y Avoid abbreviations other than the specified prefixes and postfixes in error messages.y Use system messages stored in syscomments. Use the following outline for messages:

{fully qualified procedure name} : {message}

Example

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

y Error messages should be added to the system using the following outline:sp_addmessage msg_id,severity,{message text}[,{language}[,'with_log'[,'replace']]]

y Capture the fully qualified procedure name by inserting the following code once at thebeginning of each procedure that might raise the error:

declare @sProcedureName varchar(255)select @sProcedureName = db_name()

+'.'

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

y Assign error message numbers based in the followingo Reserved 50000 thru 50999o General errors 51000 thru 51099o Import errors 52000 thru 52099o Export errors 53000 thru 53099o Services errors 58000 thru 58099o DbChangeControl Message 59000 thru 59099

Schema Scripts

y

go is the standard TSQL batch separator. Do not write scripts that depend on another separator.Go should appear on its own line of the script.y Each def_ script should define only one table. The def_ includes all constraints, keys, and

indexes for the table. A def_ script only creates an object. A def_ script must not dropstatements.

y Proc_and conv_scripts should be able to be run multiple times against the same database withno adverse effect and no errors. Always check for the existence of each object before creating itagain to avoid meaningless errors in the scripts output stream.


10/23

y An rbk_script must accompany every conv_script. As with the conv_script, the rbk_scriptmust be written so that it will execute multiple times with no adverse effects and no errors.The rbk_script will reverse or rollback all changes applied in the conv_such that full a

priorifunctionality exists in all databases touched by the conv_script.

Database Security

y Use an NT login to access the server from the application.sp_grantlogin MyDomain\SomeUser,

y Permit the NT login only in application databases where access is required.exec sp_grantdbaccess MyDomain\SomeUser

y Place users within roles or groups to give them access to datause MyDBexec sp_addrolemember AppropriateRole,

MyDomain\SomeUser

y Grant access to data to groups or roles through execution of stored proceduresuse MyDBgrant exec on MyProcedure to ApproptriateRole

y Do not use SQL Server logins, including the sa login.

y Do not grant permissions other than execute or database access to users. If statementpermissions are necessary, grant the permission to a role or group and add the correct users tothe group or role. For example, grant the Publicgroup select rights if ad hoc queries are to bepermitted by all users. Only stored procedures should insert, update or delete data.

y Do not grant permissions other than database access to users in databases other than thosewhere the stored procedures are located that the user needs to be able to execute.

y Do not grant execute permission for any stored procedures that the user does not need toexecute.

y Encrypt procedures that may reveal database user permissions or passwords or otherwisecompromise security if viewed. Do not encrypt procedures that do not meet this test.

Formating

y Use single quote characters to delimit strings. Nest single quotes to express a single quote orapostrophe within a string

set @sExample = Bills example

y Use parenthesis to increase readability, especially when working with branch conditions orcomplicated expressions.

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

y Usebegin..endblocks only when multiple statements are present within a conditional code

segment.y Limit the length of lines in all source code to 114 characters. If possible, try to leave all code

viewable without the need to horizontally scroll an 800 x 600 IDE window using a 12 pitch CourierNew font 100 characters.

y Indent one tab when indentation is required.

Whitespace


11/23

y Use spaces so that expressions read like sentences. fillfactor =25, notfillfactor=25

y Use one blank line to separate code sections.y Do not use white space in identifiers

Comments

y Use single-line comment markers where needed (--). Reserve multi-line comments (/*..*/) forblocking out sections of code.

y Comment only where the comment adds value. Don't over-comment, and try to limit comments toa single line. Choose identifier names that are self-documenting whenever possible. An overuseof multi-line comments may indicate a design that is not elegant.

DMLStatements (select, insert, update, delete)

y Fully qualify all table references with the database name and the table owner name.PUBS.dbo.TitleAuthor

master.dbo.sysdatabases

y Use ANSI join syntaxselect c.Name, a.Descriptionfrom User.dbo.TB_ADDRESS ainner join VIOLATIONS.dbo.TB_INCIDENT iOn a.Id = i.Address_Id

y Use ANSI operators

=, >, 0)

y If more than one table is involved in a fromclause, each column name must be qualified usingeither the complete table name or an alias. The alias is preferred.

y Do not use the identitycol orrowguidcol

y Always use column names in an order by clause. Avoid positional references.

Select

y Do not use a select statement to create a new table (by supplying an into table that does notexist).


12/23

y When returning a variable or computed expression, always supply a friendly alias to the client.

select @@identity as Exam_Id,(@pointsReceived/@pTotalPoints) as Average

y Opt for more descriptive alias.

select @@identity as UserId

is preferred over

select @@identity as Id

y Use the following outline for select statements. Each column in the select list should appear onits own line. Each unrelated constraint within the where clause should appear on its own line.

select {[alias.]column name}[,{[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 tinner join BusyWork.dbo.ENROLLMENTTASK eton t.Task_Id = et.Task_Id

where t.Due_Dt >=@pStartDate

and t.Due_Dt


13/23

select t.TASK_IDfrom Task.dbo.TASK tinner join Task.dbo.ENROLLMENT eton t.TASK_ID = et.TASK_ID

where et.MEMBER_ID = @pMemberIdand ((t.DUE_DT = @pEndDate)or (et.COMPLETED_FLAG = 1))

Inserts

y Always list column names within an insert statement. Never perform inserts based on columnposition alone.

y Do not call a stored procedure during an insert as in:

insert SUBSCRIBE execute SUBSCRIBERS_BUILDNEW_SYSTEM

y Use the following outline for insert statements moving values or variables into a single row. Place

each column name and value on its own line and indent both so they match as shown.

insert [into] {database name}.{owner}.{table name}({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 only1, -- example only'spring', -- example only'cross sell handle latch') - example only

y Use the following outline for inserts that move data from one table to another. Break and indentthe column lists so they match. Apply the same formatting to the fromclause as described in theselect statement.

insert [into] {{database name}.{owner}.{table name} | {alias}}({column name}[,{column name}[,}})

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

{from clause[{where clause}]}


14/23

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_HEAVYDUTYwhere Id = @pTractorId

y Provide an inline comment to explain any hardcoded value.

Updates

y Use the following outline for simple update statements. Format the where clause as describedearlier.

upd

ate {d

atabase name}.{owner}.{table name}

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

y Use the following outline for table-to-table update statements. Format the from and where clausesas described earlier.

update {database name}.{owner}.{table name}set {column} = {expression}[,

{column} = {expression}[,]]

{from clause}[{where clause}]

Example:

update PUBS.dbo.TB_TITLES

set Total_Sales = t.Total_Sales + s.Quantityfrom Pubs.dbo.TB_TITLES t

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

Deletes

y Use the following outline for simple delete statements. Format the where clause as describedearlier.


15/23

delete [from] {database name}.{owner}.{table name}{where clause}

Example

delete

from

WebLog.

dbo.TB_ARTICLE_STATISTICSwhere ARTICLE_ID = @pArticleId

y Use the following outline for table-driven delete statements. Use a subquery (formatted asdescribed earlier) rather than using the TSQL extension form.

delete [from] {database name}.{owner}.{table name}where [not] exists {correlated subquery expression}

Example:

delete WebLog.dbo.TB_ARTICLE_STATISTICS aswhere exists (select ID

from ARTICLES.dbo.TB_EXPIREDwhere ARTICLE_ID = as.ARTICLE_ID)

Transactions

y If an OLEDB client will manage a transactions always useset xact_abort onto manage a connection.

y If a transaction is necessary for a multi-statement operation, and the code will not be managed byan OLEDB client connection, use

Begin Transaction [{transaction name}]{statements}

If {error}Commit Transaction [{transaction name}]

elseRollback Transaction [{transaction name}]

Transact-SQLFlow-of-controlstatements

y Use the following outlines for if statements.

if ({condition}){statement}

elseif ({condition})

{statement}else{statement}

orif ({condition})

begin{statement}..


16/23

.{statement}

endelse

{statement}

y Use the following outlines for while statements.

while ({condition}){statement}

or

while ({condition})begin

{statement}...

{statement}end

y The case construct is not equivalent to a switch in sequential processing. The SQL casestatement is used to conditionally define a result set no to implement alternate processing.

y Use the following outlines for case statements. (Note that the SQL case construct is not aselect 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.EMAILwhen 20 then pu.EMAILelse none providedendfromWebSite.dbo.Users uinner join Users.dbo.TB_ELECTRICIAN_USER euinner join Users.dbo.TB_PARENT_USER pu

where u.DISPLAY_NAME = Smith, Bob

Cursors

y Use cursors only where a set based operation is inappropriate. (Almost never)y Never use a cursor to return data to the application. The performance hit for this is unacceptable.

LinkedServers

Use the following outline when creating l inked server:

/*


17/23

Template for use in creating an aliased SQL Server 7.0 linked server. Thesame procedure can be usedfor all lifecyle environments once built from thistemplate. User executing this procedure must be connected as sa to create alinked server and must provide the sa password of the remote system to createthe objects needed on the remote server to achieve the link. The local userthat will access the link - provided as the parameter aliasUser when callingthe procedure - must be an NT domain account. Only users connected to thelocal 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 belogged into the domain as an interactive workstation user or an NT service.The domain account cannot be impersonated nor specified in a connectionstring. 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. Allpermissions needed on the linked server must be explicitly added to thisprocedure. If this SQL Server login already exists on the remote server, allpre-existing permissions in all databases on that server will be lost whenthis procedure is executed. The only place this user's password will bestored is in the linked server connection on the local server in encryptedform. This user should not be used in any other context to avoid acompromised security condition.

replace the literal with the name to be used inthe server position of all 4 partqualified calls to the remote server (server.database.owner.object) --literal occurs 5 times in this script

replace the literal with the database to beaccessed on the remote server.-- literal occurs once in this script

-- stored procedure call prototypeexec lnk '',

'','','',''

*/

use admingoif (objectproperty(object_id('lnk'),'IsProcedure')= 1)

drop procedure lnkgo

create procedure lnk@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 logindeclare @returnCode int,


18/23

@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 machineselect @linkAlias = ''-- remote calls will only have access to this databaseset @targetDb = ''

-- add the NT logon as a local login if it is not already a local loginif (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 hereset @cmd = 'if (select suser_id(''' +@sqlUser + ''')) is null execsp_addlogin '

+@sqlUser + ', ' +@sqlPassword+ ', ' +@targetDb+ ' if (select user_id(''' +@sqlUser + ''')) is null execsp_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 on to '+@sqlUser-- stored procedure - example:-- + ' grant exec on byroyalty to ' +@sqlUser+ ' grant exec on to ' +@sqlUser

-- add user and permissions to remote server via osqlset @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 existsexec @returnCode = master.dbo.sp_helpserver @linkAliasif (@returnCode = 0)

beginexec master.dbo.sp_droplinkedsrvlogin @linkAlias,

@aliasUser

exec master.dbo.sp_droplinkedsrvlogin @linkAlias, NULLexec master.dbo.sp_dropserver @linkAliasend

EXEC master.dbo.sp_addlinkedserver@server = @linkAlias,@srvproduct = '',@provider = 'SQLOLEDB',@datasrc = @targetServer,@catalog = @targetDb


19/23

EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = @linkAlias,@useself = 'false',@locallogin = @AliasUser,@rmtuser = @sqlUser,@rmtPassword = @sqlPassword

return

SendingEmailNotifications

When it is necessary to originate an administrative email message within a stored procedure, alwaysreference an address associated to a sysoperators member. This will enable the same code to used indevelopment and testing without sending misleading messages to production personnel because thesame operator can be configures to have a different email address in different software lifecycleenvironments. 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 nullif@@rowcount > 0

beginselect @EmailTo = email_addressfrom 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

DevelopmentEnvironments

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 applicationinstalled on the desktop. The desktop will serve as the Unit Test platform for all schema, stored procedureand script development, testing, and debugging.

The network login share (normally mapped to H:) is specified as the SourceSafe Current WorkingDirectory. This location is chosen because it is backed up to tape periodically. This means that objectsthat are checked out as part of a development project will enjoy a closer approximation to the level of


20/23

security as objects checked into SourceSafe. Checking in to SourceSafe is one step better as it assures alocal copy of all objects and a copy stored in the SourceSafe repository and has no dependency on anuncoordinated backup operation.

System test is intended as a front end/middle ware/application server development tool. No databasechanges 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 controlsystem must be checked out and the revisions completed and tested in the Unit Test environment. Thiswill provide a greater level of stability for software developers than a chaotic environment where schemamight 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 andwithin the context of the company wide change control policy and procedures.

ClassModel

y Database schema will be modeled using the UML extensions of Visio.y The class model will be maintained on the intranet and available to all.y The class model can document the logical structure of:

o Tables Attributes

Column name Data type

Associations (Relationships between classes) Relationship (a line)

direction of dependency (an arrow) stereotype (a verb, works for,member of, )

Cardinality 1(1..1 or implicit) unknown but bound none to some finite range (0..1,1..2,1..n) one or more - unlimited (1..*) none or more unlimited (0..*)

Roles Foreign Keys Parent-child Aggregation

Stored Procedures expressed as operations Parameters

What is Returned

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 beimplemented in the Unit Test environment using GUI tools or ad hoc techniques at the pleasure of thedeveloper. Such changes will need to be scripted and the scripts tested before migrating them to theIntegration environment. For this reason, it will generally be more efficient to always script changes andavoid EM or other non scripted techniques.

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


21/23

y Existing objects scripts are checked out of SourceSafe and the latest version is moved to theworking directory. When checked out, include a brief description of the development project in theSourceSafe checkout dialogs comment box for each object checked out.

y The objectives are implemented in the Unit Test Environment.y Test plans are prepared/modified and executed for all stored procedures and scripts.y Return all objects to the SourceSafe system

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 productionenvironment the Database Team will adhere to the following procedure at all times. These procedureswill not only help protect The organization from the hazards of missed manual steps, but enable anymember on the database team to provide support for any change and help us as we work to establish astandard for our SourceSafe implementation. This procedure has the endorsement of QA and ProductionOperations.

1. All database changes will have a fully endorsed and properly numbered PCR(ProductionChange 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 .sqlextension. The script will be tested before the change is delivered to QA. The script will be themethod 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//Conversions/.sql at the time the project is delivered to QA and then also added to the samesourceSafe repository at the project path $/PROJECT/Database/Production//Conversions//.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 isknown.

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 isdelivered to QA. It will then be the option of QA to test the rollback procedure as deemedappropriate. This script will be stored in the SourceSafe repository -- share name\\Barney\VSS_PROJECT -- at the project path $/PROJECT/Database/Production//Conversions/.rbk at the time the project isdelivered to QA and then also added to the same sourceSafe repository at the project path$/PROJECT/Database/Production//Conversions//.rbk at the time it is delivered to each Production Server. The PCR number will be addedto 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 meanthat a Word document covering the steps needed to reimplement the changes should a newserver 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 SourceSafeProject and any necessary scripts for this recovery process will be added or modified in supportof the document.

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

UnitTest Plans


22/23

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

The test document will define:y 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 usingthe populate scripts with an satisfactory population level. Any other setup orconfiguration should be noted on the test document.

y 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 revisionis necessary, the test cycle should be restarted after all changes are made.

y Each test required in the test cycle with expected result.y An explanation of any variance from the expected result.y 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 reviewsare intended to improve the quality of the applications and as a learning experience for all databasedevelopers. The code review will not be a tool used to evaluate any person, rather to focus on technicalconsideration of code. The review will not cover how a solution should be coded, rather it will examine thecode to identify defects in coding standard compliance, logic, performance, portability, audit-ability, errorhandling, architectural compatibility, and security. Everyone that writes SQL that accesses a database isexpected 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 SteveMcConnell (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. TheAuthor will print a copy of the code and present it to each reviewer 24 hours before the review isscheduled. The print-out is to be in landscape format in a 10 or 12 pitch fixed width font with numberedlines and numbered pages. The lead role during the code review is the Moderator. This person must keepthe review meeting on topic and on time. The Moderator will also lead a review of identified issues andassure that all tasks are assigned as identified in the review. At the conclusion of each meeting, theModerator is responsible for assigning roles for the next review meeting. A second role during the reviewis 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 anddeliver to all persons involved in the review as well as direct line management. A final role, that ofReviewer, 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 beidentified 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 teammembers. It may be necessary for a person to assume multiple roles. For example, the Moderator andScribe 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 hoursScribe 2 hours

Author 2 hoursReviewer 2-3 hours


23/23

Evaluation

It is important for personal growth and for the efficacy of the team that all members are kept informed oftheir 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 teammembers.

Development Process

y New development (iterative process)o Define the data requirements from the user requirements.o Build a class model.o Build the tables on the local (Unit test) system.o Create the procedures needed to deliver the data.o Create a test plan for each stored procedure.o Test each procedure using the test plan.o Script all new database objects into SourceSafe using the scripting/SourceSafe tool seto The change is not introduced to other environments until a SourceSafe version Is labeled

y Bug fixes/enhancements (non-iterative process)o Reproduce bug or current behavior on the local (unit test) database.o Identify and evaluate as many solutions as possible.o Implement the change on the local database.o Modify the test plan as needed to properly test the change.o Unit test the change by completing the test plan.o If the change requires scheme changes, update the class model.o If the change requires a data conversion, add the conversion to the database conversion

script folder and test.o Move all changes into SourceSafe.o The change is not introduced to other environments until a SourceSafe version Is labeledo All pending changes up to the time of the label are built in the appropriate environment

using the database upgrade process.

database coding standard and guideline

Documents