C H A P T E R 2

  

Best Practices for
Database Programming
Software development is not just a practical discipline performed by coders, but also an area of
academic research and theory. There is now a great body of knowledge concerning software
development, and lengthy academic papers have been written to propose, dissect, and discuss different
approaches to development. Various methodologies have emerged, including test-driven development
(TDD), agile and extreme programming (XP), and defensive programming, and there have been
countless arguments concerning the benefits afforded by each of these schools of thought.
The practices described in this chapter, and the approach taken throughout the rest of this book, are
most closely aligned with the philosophy of defensive programming. However, the topics discussed here
can be applied just as readily in any environment. While software theorists may argue the finer
differences between different methodologies (and undoubtedly, they do differ in some respects), when it
comes down to it, the underlying features of good programming remain the same whatever
methodology you apply.
I do not intend to provide an exhaustive, objective guide as to what constitutes best practice, but
rather to highlight some of the standards that I believe demonstrate the level of professionalism that
database developers require in order to do a good job. I will present the justification of each argument
from a defensive point of view, but remember that they are generally equally valid in other
environments.
Defensive Programming
Defensive programming is a methodology used in software development that suggests that developers
should proactively anticipate and make allowances for (or “defend against”) unforeseen future events.
The objective of defensive programming is to create applications that can remain robust and effective,
even when faced with unexpected situations.
Defensive programming essentially involves taking a pessimistic view of the world—if something
can go wrong, it will: network resources will become unavailable halfway through a transaction; required
files will be absent or corrupt; users will input data in any number of ways different from that expected,

groups, including other developers or technical teams, consultants, end users, and
management. Each of these different groups may have different implicit
assumptions that might not be considered by a closed development team.
• Assum
ptions should be avoided wherever possible. If an application requires a
certain condition to be true in order to function correctly, there should be an
explicit assertion to this effect, and relevant code paths should be inserted to
check and act accordingly based on the result.
• Applica
tions should be built from short, highly cohesive, loosely coupled modules.
Modules that are well encapsulated in this way can be thoroughly tested in
isolation, and then confidently reused throughout the application. Reusing
specific code modules, rather than duplicating functionality, reduces the chances
of introducing new bugs.
Throughout the remainder of this chapter, I'll be providing simple examples of what I believe to be
best
practices demonstrating each of these principles, and these concepts will be continually
reexamined in later chapters of this book.
Attitudes to Defensive Programming
The key advantages of taking a defensive approach to programming are essentially twofold:
• Defensive applications are ty
pically robust and stable, require fewer essential bug
fixes, and are more resilient to situations that may otherwise lead to expensive
failures or crashes. As a result, they have a long expected lifespan, and relatively
cheap ongoing maintenance costs.
• In many ca
ses, defensive programming can lead to an improved user experience.
By actively foreseeing and allowing for exceptional circumstances, errors can be
caught before they occur, rather than having to be handled afterward. Exceptions
can be isolated and handled with a minimum negative effect on user experience,

contain more total lines of code than other applications, but all of that code should be well designed,
with a clear purpose. Note that the label of “defensive programming” is sometimes misused: the
addition of unnecessary checks at every opportunity without consideration or justification is not
defensive programming. Such actions lead to code that is both complex and rigid. Remember that true
defensive programming promotes simplicity, modularization, and code reuse, which actually reduces
code bloat.
Defensive programming hides bugs that then go unfixed, rather than making them
visible.
This is perhaps the most common misconception applied to defensive practices, which manifests from a
failure to understand the fundamental attitude toward errors in defensive applications. By explicitly
identifying and checking exceptional scenarios, defensive programming actually takes a very proactive
approach to the identification of errors. However, having encountered a condition that could lead to an
exceptional circumstance, defensive applications are designed to fail gracefully—that is, at the point of
development, potential scenarios that may lead to exceptions are identified and code paths are created
CHAPTER 2  BEST PRACTICES FOR DATABASE PROGRAMMING
26
to handle them. To demonstrate this in practical terms, consider the following code listing, which
describes a simple stored procedure to divide one number by another:
CREATE PROCEDURE Divide (
@x decimal(18,2),
@y decimal(18,2)
)
AS BEGIN
SELECT @x / @y
END;
GO
Based on the code as written previously, it would be very easy to cause an exception using this
procedure if, for example, the supplied valu
e of @y was 0. If you were simply trying to prevent the error
message from occurring, it would be possible to consume (or “swallow”) the exception in a catch block,

IF @x IS NULL OR @y IS NULL
BEGIN
PRINT 'Please supply values for @x and @y';
RETURN;
END
CHAPTER 2  BEST PRACTICES FOR DATABASE PROGRAMMING
27
IF @y = 0
BEGIN
PRINT '@y cannot be equal to 0';
RETURN;
END

BEGIN TRY
SELECT @x / @y
END TRY
BEGIN CATCH
PRINT 'An unhandled exception occurred';
END CATCH
END;
GO
For the purposes of the preceding example, each a
ssertion was accompanied by a simple PRINT
statement to advise which of the conditions necessary for the procedure to execute failed. In real life,
these code paths may handle such assertions in a number of ways—typically logging the error, reporting
a message to the user, and attempting to continue system operation if it is possible to do so. In doing so,
they prevent the kind of unpredictable behavior associated with an exception that has not been
expected.
Defensive programming can be co
ntrasted to the fail fast methodology, which focuses on

hangs, or because some application fails to shut down correctly. However, the
same tolerance that is shown to personal desktop software is not typically extended
to corporate database applications. Recent highly publicized scandals in which
bugs have been exploited in the systems of several governments and large
organizations have further heightened the general public’s ultrasensitivity toward
anything that might present a risk to database integrity.
Any bugs that do exist in database ap
plications can have more severe
consequences than in other software. It can be argued that people are absolutely
right to be more worried about database bugs than bugs in other software. An
unexpected error in a desktop application may lead to a document or file becoming
corrupt, which is a nuisance and might lead to unnecessary rework. But an
unexpected error in a database may lead to important personal, confidential, or
sensitive data being placed at risk, which can have rather more serious
consequences. The nature of data typically stored in a database warrants a
cautious, thorough approach to development, such as defensive programming
provides.
Designing for Longevity
Consumer software applications have an increasingly short expected shelf life, with compressed release
cycles pushing out one release barely before the predecessor has hit the shelves. However, this does not
have to be the case. Well-designed, defensively programmed applications can continue to operate for
many years. In one organization I worked for, a short-term tactical management information data store
was created so that essential business reporting functions could continue while the organization’s systems
went through an integration following a merger. Despite only being required for an immediate post-merger
period, the (rather unfortunately named) Short Term Management Information database continued to be
used for up to ten years later, as it remained more reliable and robust than subsequent attempted
replacements.
And let that be a lesson in choosing descriptive names for your databases that won’t age with time!
Best Practice SQL Programming Techniques
Having looked at some of the theory behind different software methodologies, and in particular the

OrderDate DATE);

INSERT INTO Orders(OrderID, CustID, OrderDate) VALUES
(1, 1, '2008-01-01'),
(2, 1, '2008-03-04'),
(3, 2, '2008-03-07');
GO
Now consider the following query to select a list of every cu
stomer order, which uses columns from
both tables:
SELECT
Name,
Address,
OrderID
FROM
Customers c
JOIN Orders o ON c.CustID = o.CustID;
GO
CHAPTER 2  BEST PRACTICES FOR DATABASE PROGRAMMING
30
The query executes successfully and we get the results expected:
Bob Smith Flat 1, 27 Heigham Street 1
Bob Smith Flat 1, 27 Heigham Street 2
Tony James 87 Long Road 3
But what is the hidden assumption? The column names listed in the SELECT query were not qualified
with table names, so what would happen if the table structure were to change in the future? Suppose
that an Address column were added to the Orders table to enable a separate delivery address to be
attached to each order, rather than relying on the address in the Customers table:
ALTER TABLE Orders ADD Address varchar(255);
GO

(1, 'abc'), (2, 'def'), (3, 'ghi'), (4, 'jkl');
GO
Now suppose that every change made to the M
ainData table was to be recorded in an associated
ChangeLog table. The following code demonstrates this structure, together with a mechanism to
automatically populate the ChangeLog table by means of an UPDATE trigger attached to the MainData table:
CREATE TABLE ChangeLog(
ChangeID int IDENTITY(1,1),
RowID int,
OldValue char(3),
NewValue char(3),
ChangeDate datetime);
GO

CREATE TRIGGER DataUpdate ON MainData
FOR UPDATE
AS
DECLARE @ID int;
SELECT @ID = ID FROM INSERTED;

DECLARE @OldValue varchar(32);
SELECT @OldValue = Value FROM DELETED;

DECLARE @NewValue varchar(32);
SELECT @NewValue = Value FROM INSERTED;

INSERT INTO ChangeLog(RowID, OldValue, NewValue, ChangeDate)
VALUES(@ID, @OldValue, @NewValue, GetDate());
GO
We can test the trigger by running a simple U

(3 row(s) affected)