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

• Case
• Use all upper case for table and view names
• Use mixed case for column names and variables
• Use mixed case for stored procedure name
• Use lower case for other names except use the same case as indicated above where a table or column is used in another object’s name

• Prefixes and suffixes:
• 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)

• 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

• Save all scripts using the .sql extension
• 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
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

• All other suffixes should be whole words

Name, Type, Flag, etc. …

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

• 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 the primary join table. Murphy's Law as applied to procedure names: The longest, most inconvenient procedure name is usually the "right" one.

• The audience might typically be one of the following:
• Import
• Export
• Custom
• Operations
• Report
• System (not directly available to the front end)
• Avoid abbreviations where possible though acronyms that have meaning to the business are acceptable.

Tables

• 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,

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))

• The preferred table prefix is TB_ however it is desirable to use an alternate prefix to provide clarity in staging environments. This will help differentiate a staging container from a permanent storage containter.

TSYS_REC_30, EDS_REC_40, PVS_ACCOUNT, etc.

• Object names should include underscores between words.
• Name all tables in the singular form.

TB_CALLING_CARD not TB_CALLING_CARDS

• If a column references an Id in another table, use the full table name. For example, use Title_Id in table TB_AUTHOR to reference column Id or Title_Id in table TB_TITLE.

• Explicitly name constraints. A table or column constraint name will include the table name(s) that it references and the columns affected by the constraint. Separate each table and column in a constraint name with two underscores (__) to differentiate from the single underscore (_) that may be within the table or column’s name.

• A foreign key name will identify both tables participating in a foreign key, the column(s) involved in the relationship, and the direction. The foreign key table (table where constraint is attached) appears first.

fk_TB_COURSE__Educator_Id__to__TB_USER__Id

• Default constraints must be defined at the column level

• Define all Constraints other than defaults at the table level. Constraints that apply to more than one column must be defined and the table level. The standard advocates this method to avoid supporting two methods in the standard. This will provide a more consistent development and conversion interface.

• 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.

• Never use a SQL Server reserved word as an identifier name. (Refer to SQL Server Books on Line for a complete listing).
• Unique constraints are preferred over unique indexes for performance reasons, though if a table’s data is accessed by the non primary key unique column(s) a unique index may be appropriate.

• Altering Tables

• alter table should be used in scripts that will be used for upgrading existing table, not when creating tables.

• Use the following outline to drop an existing column or table constraint

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

'IsConstraint') is not null)

alter table {table name}

drop constraint {constraint name}

• 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}]

• 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}

• 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})]

• Use the following outline for altering a default on an existing column. (Note that It may be necessary to remove foreign keys before altering a column and in a few cases it may be necessary to duplicate the table with the desired new structure and move the data to the new table. This latter operation can be scripted using the EM GUI in a unit test environment 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}

• Alter table examples:

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

• Use the following outline for creating 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)

• Explicitly name all indexes and include the table name and all indexed columns in index order.

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

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

Stored Procedures (and other dml scripts)

• Use the following outline for creating stored procedures

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

• Always specify the owner when creating a procedure script
• Do not use alter procedure. Scripts should be structured so that procedures are dropped and recreated.
• Do not use temporary stored procedures.

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

• Do not use output parameters. Any information returned to the client should be done via a result set.

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

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

• Never use the recompile option.

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

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

• Place set statements before any executing code in the procedure.
• Fully qualify all table and view references with the database name and the table owner name.

Northwind.dbo.TB_ORDER

master.dbo.sysdatabases

• Avoid calling stored procedures from stored procedures because error handling is very unpredictable in such circumstances. I procedures must be nested, fully qualify the procedure name.

• 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.

• Enumerate all column lists. Do not use the ‘*’ wildcard.

• 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 immediately after the statement. Capturing more than one global variable one at a time will produce erroneous results.

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

• 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).

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

• Avoid abbreviations other than the specified prefixes and postfixes in error messages.
• 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?

• Error messages should be added to the system using the following outline:

sp_addmessage msg_id,
severity,
{message text}[,

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

• Capture the fully qualified procedure name by inserting the following code once at the

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)

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

Schema Scripts

• go is the standard TSQL batch separator. Do not write scripts that depend on another separator. Go should appear on it’s own line of the script.

• 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 drop statements.

• Proc_ and conv_ scripts should be able to be run multiple times against the same database with no adverse effect and no errors. Always check for the existence of each object before creating it again to avoid meaningless errors in the scripts output stream.

• An rbk_ script must accompany every conv_ script. As with the conv_ script, the rbk_ script must 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 priori functionality exists in all databases touched by the conv_ script.

Database Security

• Use an NT login to access the server from the application.

sp_grantlogin ‘MyDomain\SomeUser’,’

• Permit the NT login only in application databases where access is required.

exec sp_grantdbaccess ‘MyDomain\SomeUser’

• Place users within roles or groups to give them access to data

use MyDB

exec sp_addrolemember ‘AppropriateRole’,

‘MyDomain\SomeUser’

• Grant access to data to groups or roles through execution of stored procedures

use MyDB

grant exec on MyProcedure to ‘ApproptriateRole’

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

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

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

• Do not grant execute permission for any stored procedures that the user does not need to execute.

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

Formating

• Use single quote characters to delimit strings. Nest single quotes to express a single quote or apostrophe within a string

set @sExample = ‘Bill’’s example’

• Use parenthesis to increase readability, especially when working with branch conditions or complicated expressions.

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

• Use begin..end blocks only when multiple statements are present within a conditional code segment.

• 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 Courier New font – 100 characters.

• Indent one tab when indentation is required.

Whitespace

• Use spaces so that expressions read like sentences. fillfactor = 25, not fillfactor=25

• Use one blank line to separate code sections.

• Do not use white space in identifiers

Comments

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

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

DML Statements (select, insert, update, delete)

• Fully qualify all table references with the database name and the table owner name.

PUBS.dbo.TitleAuthor

master.dbo.sysdatabases

• Use ANSI join syntax

select c.Name, a.Description

from User.dbo.TB_ADDRESS a

inner join VIOLATIONS.dbo.TB_INCIDENT i

On a.Id = i.Address_Id

• Use ANSI operators

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

• A correlated subquery using exists or not exists is preferred over the equivalent in or not in subquery due to performance degradation porential in some cases using not in.

• Avoid the use of cross joins if possible.

• When a result set is not needed, use syntax that does not return a result set.

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)

• If more than one table is involved in a from clause, each column name must be qualified using either the complete table name or an alias. The alias is preferred.

• Do not use the identitycol or rowguidcol
• Always use column names in an order by clause. Avoid positional references.

Select

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

• When returning a variable or computed expression, always supply a friendly alias to the client.
  
  select @@identity as Exam_Id,

(@pointsReceived / @pTotalPoints) as Average

• Opt for more descriptive alias.

select @@identity as UserId

is preferred over

select @@identity as Id

• Use the following outline for select statements. Each column in the select list should appear on its’ 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 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

• When dealing with select statements used as conditions or subquerie, use more convenient formatting than the outline above where necessary. One line select statements are fine as long as they are easy enough to read.

• Dependent constraints within the where clause should appear together offset by parenthesis. Use additional indentation if necessary.
  
  select t.TASK_ID
  from Task.dbo.TASK t

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

• Always list column names within an insert statement. Never perform inserts based on column position alone.

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

insert SUBSCRIBE execute SUBSCRIBERS_BUILDNEW_SYSTEM

• 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 only

1, -- example only

'spring', -- example only

'cross sell handle latch') –- example only

• Use the following outline for inserts that move data from one table to another. Break and indent the column lists so they match. Apply the same formatting to the from clause as described in the select 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}]}

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

• Use the following outline for simple update statements. Format the where clause as described earlier.
  
  update {database 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

• Use the following outline for table-to-table update statements. Format the from and where clauses as 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.Quantity
from Pubs.dbo.TB_TITLES t

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

Deletes

• Use the following outline for simple delete statements. Format the where clause as described earlier.
  
  delete [from] {database name}.{owner}.{table name}
  {where clause}

Example

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

• Use the following outline for table-driven delete statements. Use a subquery (formatted as described 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 as
where exists (select ID

from ARTICLES.dbo.TB_EXPIRED

where ARTICLE_ID = as.ARTICLE_ID)

Transactions

• If an OLEDB client will manage a transactions always use

set xact_abort on

to manage a connection.

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

Begin Transaction [{transaction name}]

{statements}

If {error}

Commit Transaction [{transaction name}]

else

Rollback Transaction [{transaction name}]

Transact-SQL Flow-of-control statements

• Use the following outlines for if statements.
  
  if ({condition})

{statement}
else

if ({condition})

{statement}
else

{statement}

or

if ({condition})

begin

{statement}

.

.

.

{statement}
end

else

{statement}

• Use the following outlines for while statements.
  
  while ({condition})

{statement}

or

while ({condition})

begin

{statement}

.

.

.

{statement}
end

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

• Use the following outlines for case statements. (Note that the SQL case construct is not a

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

• Use cursors only where a set based operation is inappropriate. (Almost never)

• Never use a cursor to return data to the application. The performance hit for this is unacceptable.

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

• Database schema will be modeled using the UML extensions of Visio.

• The class model will be maintained on the intranet and available to all.

• The class model can document the logical structure of:
• 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 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:

• Existing objects scripts are checked out of SourceSafe and the latest version is moved to the working directory. When checked out, include a brief description of the development project in the SourceSafe checkout dialog’s comment box for each object checked out.

• The objectives are implemented in the Unit Test Environment.

• Test plans are prepared/modified and executed for all stored procedures and scripts.

• 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 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

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

• Bug fixes/enhancements (non-iterative process)
• Reproduce bug or current behavior on the local (unit test) database.
• Identify and evaluate as many solutions as possible.
• Implement the change on the local database.
• Modify the test plan as needed to properly test the change.
• Unit test the change by completing the test plan.
• If the change requires scheme changes, update the class model.
• If the change requires a data conversion, add the conversion to the database conversion script folder and test.
• Move all changes into SourceSafe.
• The change is not introduced to other environments until a SourceSafe version Is labeled
• All pending changes up to the time of the label are built in the appropriate environment using the database upgrade process.