Aitchison
Machanic
SQL Server 2008 Development
Companion
eBook Available
Expert
trim = 7.5" x 9.25" spine = 0.84375" 456 page count
THE EXPERT’S VOICE
®
IN SQL SERVER
Expert

SQL Server 2008
Development
Alastair Aitchison
and
Adam Machanic
Advanced SQL Server techniques for
database professionals
this print for content only—size & color not accurate
BOOKS FOR PROFESSIONALS BY PROFESSIONALS
®
US $49.99
Shelve in:
Databases / SQL Server
User level:
Intermediate / Advanced
www.apress.com
SOURCE CODE ONLINE
Companion eBook


uncover some of the interesting issues that can arise in these situations.
I’ve worked hard on this book, to make it useful to readers of all skill levels.
Beginner, expert, or in between, you’ll find something of use in this book. My
hope is that it helps you become truly an expert SQL Server developer.
Alastair Aitchison
THE APRESS ROADMAP
Expert
SQL Server 2008 Development
Beginning T-SQL 2008
Accelerated SQL Server 2008
Pro T-SQL 2008
Programmer’s Guide
SQL Server 2008
Transact-SQL Recipes
CYAN
MAGENTA
YELLOW
BLACK
PANTONE 123 C
Alastair Aitchison,
Author of
Beginning Spatial With
SQL Server 2008
Adam Machanic,
Author of
Expert SQL Server 2005
Development


Expert SQL Server

Lead Editor: Jonathan Gennick
Technical Reviewer: Evan Terry
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell,
Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes,
Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft,
Matt Wade, Tom Welsh
Coordinating Editor: Mary Tobin
Copy Editor: Damon Larson
Compositor: Bytheway Publishing Services
Indexer: Barbara Palumbo
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th
Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail orders-ny@springer-
sbm.com, or visit http://www.springeronline.com.
For information on translations, please e-mail [email protected], or visit http://www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional
use. eBook versions and licenses are also available for most titles. For more information, reference our
Special Bulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have
any liability to any person or entity with respect to any loss or damage caused or alleged to be caused
directly or indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.com. You will need to
answer questions pertaining to this book in order to successfully download the code. For Clare and Douglas

 CONTENTS

Architecture Revisited .......................................................................................................1
Coupling.................................................................................................................................................... 3
Cohesion................................................................................................................................................... 4
Encapsulation ........................................................................................................................................... 5
Interfaces.................................................................................................................................................. 5
Interfaces As Contracts ............................................................................................................................ 6
Interface Design ....................................................................................................................................... 6
Integrating Databases and Object-Oriented Systems........................................................8
Data Logic............................................................................................................................................... 10
Business Logic........................................................................................................................................ 11
Application Logic .................................................................................................................................... 12
The “Object-Relational Impedance Mismatch” ...............................................................12
Are Tables Really Classes in Disguise? .................................................................................................. 13
Modeling Inheritance.............................................................................................................................. 14
ORM: A Solution That Creates Many Problems................................................................17
v
 CONTENTS
Introducing the Database-As-API Mindset.......................................................................18
The Great Balancing Act ..................................................................................................19
Performance........................................................................................................................................... 19
Testability ............................................................................................................................................... 20
Maintainability ........................................................................................................................................ 20
Security .................................................................................................................................................. 21
Allowing for Future Requirements.......................................................................................................... 21
Summary .........................................................................................................................22
Best Practices for Database Programming......................................................................23
 Chapter 2: Best Practices for Database Programming .........................................23
Defensive Programming ..................................................................................................23
Attitudes to Defensive Programming...................................................................................................... 24
Why Use a Defensive Approach to Database Development?.................................................................. 27

Dynamic Management Views (DMVs)..................................................................................................... 62
Extended Events ..................................................................................................................................... 63
Data Collector ......................................................................................................................................... 65
Analyzing Performance Data ...........................................................................................67
Capturing Baseline Metrics .................................................................................................................... 67
Big-Picture Analysis ............................................................................................................................... 68
Granular Analysis.................................................................................................................................... 68
Fixing Problems: Is It Sufficient to Focus on the Obvious?..................................................................... 70
Summary .........................................................................................................................70
 Chapter 4: Errors and Exceptions .........................................................................71
Exceptions vs. Errors .......................................................................................................71
How Exceptions Work in SQL Server ...............................................................................72
Statement-Level Exceptions................................................................................................................... 73
Batch-Level Exceptions .......................................................................................................................... 73
vii
 CONTENTS
Parsing and Scope-Resolution Exceptions ............................................................................................. 75
Connection and Server-Level Exceptions ............................................................................................... 76
The XACT_ABORT Setting....................................................................................................................... 77
Dissecting an Error Message.................................................................................................................. 78
Error Number..................................................................................................................................... 78
Error Level ......................................................................................................................................... 79
Error State ......................................................................................................................................... 79
Additional Information ....................................................................................................................... 80
SQL Server’s RAISERROR Function......................................................................................................... 81
Formatting Error Messages ............................................................................................................... 82
Creating Persistent Custom Error Messages..................................................................................... 83
Logging User-Thrown Exceptions...................................................................................................... 85
Monitoring Exception Events with Traces .............................................................................................. 85
Exception Handling..........................................................................................................85

Do You Really Need Encryption?....................................................................................121
What Should Be Protected?.................................................................................................................. 121
What Are You Protecting Against?........................................................................................................ 122
SQL Server 2008 Encryption Key Hierarchy...................................................................123
The Automatic Key Management Hierarchy ......................................................................................... 123
Symmetric Keys, Asymmetric Keys, and Certificates...................................................................... 124
Database Master Key....................................................................................................................... 125
Service Master Key.......................................................................................................................... 125
Alternative Encryption Management Structures................................................................................... 125
Symmetric Key Layering and Rotation ............................................................................................ 126
Removing Keys from the Automatic Encryption Hierarchy.............................................................. 126
Extensible Key Management ........................................................................................................... 127
Data Protection and Encryption Methods ......................................................................128
Hashing................................................................................................................................................. 129
Symmetric Key Encryption ................................................................................................................... 130
ix
 CONTENTS
Asymmetric Key Encryption.................................................................................................................. 134
Transparent Data Encryption................................................................................................................ 136
Balancing Performance and Security ............................................................................139
Implications of Encryption on Query Design..................................................................145
Equality Matching Using Hashed Message Authentication Codes........................................................ 148
Wildcard Searches Using HMAC Substrings......................................................................................... 153
Range Searches.................................................................................................................................... 157
Summary .......................................................................................................................158
 Chapter 7: SQLCLR: Architecture and Design Considerations.............................159
Bridging the SQL/CLR Gap: The SqlTypes Library..........................................................160
Wrapping Code to Promote Cross-Tier Reuse ...............................................................161
The Problem ......................................................................................................................................... 161
One Reasonable Solution...................................................................................................................... 161

Performance Implications of Parameterization and Caching ............................................................... 203
Supporting Optional Parameters....................................................................................205
Optional Parameters via Static T-SQL .................................................................................................. 206
Going Dynamic: Using EXECUTE ........................................................................................................... 212
SQL Injection......................................................................................................................................... 218
sp_executesql: A Better EXECUTE ........................................................................................................ 220
Performance Comparison..................................................................................................................... 223
Dynamic SQL Security Considerations...........................................................................230
Permissions to Referenced Objects...................................................................................................... 230
Interface Rules ..................................................................................................................................... 230
Summary .......................................................................................................................232
 Chapter 9: Designing Systems for Application Concurrency ..............................235
The Business Side: What Should Happen When Processes Collide?.............................236
Isolation Levels and Transactional Behavior .................................................................237
Blocking Isolation Levels ...................................................................................................................... 239
xi
 CONTENTS
READ COMMITTED Isolation............................................................................................................. 239
REPEATABLE READ Isolation............................................................................................................ 239
SERIALIZABLE Isolation.................................................................................................................... 240
Nonblocking Isolation Levels................................................................................................................ 241
READ UNCOMMITTED Isolation ........................................................................................................ 241
SNAPSHOT Isolation ........................................................................................................................ 242
From Isolation to Concurrency Control ................................................................................................. 242
Preparing for the Worst: Pessimistic Concurrency ........................................................243
Progressing to a Solution ..................................................................................................................... 244
Enforcing Pessimistic Locks at Write Time .......................................................................................... 249
Application Locks: Generalizing Pessimistic Concurrency ................................................................... 250
Hoping for the Best: Optimistic Concurrency.................................................................259
Embracing Conflict: Multivalue Concurrency Control ....................................................266

How Does a Spatial Index Work?.......................................................................................................... 313
Optimizing the Grid............................................................................................................................... 315
Summary .......................................................................................................................319
 Chapter 11: Working with Temporal Data...........................................................321
Modeling Time-Based Information ................................................................................321
SQL Server’s Date/Time Data Types..............................................................................322
Input Date Formats ............................................................................................................................... 323
Output Date Formatting ........................................................................................................................ 325
Efficiently Querying Date/Time Columns .............................................................................................. 326
Date/Time Calculations......................................................................................................................... 329
Truncating the Time Portion of a datetime Value ............................................................................ 330
Finding Relative Dates..................................................................................................................... 332
How Many Candles on the Birthday Cake?...................................................................................... 335
Defining Periods Using Calendar Tables........................................................................336
Dealing with Time Zones ...............................................................................................341
xiii
 CONTENTS
Storing UTC Time.................................................................................................................................. 343
Using the datetimeoffset Type.............................................................................................................. 344
Working with Intervals...................................................................................................346
Modeling and Querying Continuous Intervals....................................................................................... 347
Modeling and Querying Independent Intervals..................................................................................... 354
Overlapping Intervals............................................................................................................................ 358
Time Slicing.......................................................................................................................................... 362
Modeling Durations........................................................................................................365
Managing Bitemporal Data ............................................................................................366
Summary .......................................................................................................................370
 Chapter 12: Trees, Hierarchies, and Graphs .......................................................371
Terminology: Everything Is a Graph...............................................................................371
The Basics: Adjacency Lists and Graphs .......................................................................373

Index ................................................................................................................419

About the Author
 Alastair Aitchison is a freelance technology consultant based in Norwich, England. He has experience
across a wide variety of software and service platforms, and has worked with SQL Server 2008 since the
earliest technical previews were made publicly available. He has implemented various SQL Server
solutions requiring highly concurrent processes and large data warehouses in the financial services
sector, combined with reporting and analytical capability based on the Microsoft business intelligence
stack. Alastair has a particular interest in analysis of spatial data, and is the author of Beginning Spatial
with SQL Server 2008 (Apress, 2009). He speaks at user groups and conferences, and is a highly active
contributor to several online support communities, including the Microsoft SQL Server Developer
Center forums.
xvi

About the Technical Reviewer  Evan Terry is the Chief Technical Consultant at The Clegg Company,
specializing in data management, information and data architecture, database
systems, and business intelligence. His past and current clients include the State
of Idaho, Albertsons, American Honda Motors, and Toyota Motor Sales, USA. He is
the coauthor of Beginning Relational Data Modeling, has published several articles
in DM Review, and has presented at industry conferences and conducted private
workshops on the subjects of data and information quality, and information
management. He has also been the technical reviewer of several Apress books
relating to SQL Server databases. For questions or consulting needs, Evan can be
reached at [email protected].
xvii
 CONTENTS
Acknowledgments

material to fill an entire book, so I'm not even going to try doing so. Instead, I’m going to concentrate on
what I believe you need to know to create high-quality database applications, based on my own practical
experience. I’m not going to waste pages discussing the ins and outs of some obscure or little-used
feature, unless I can show you a genuine use case for it. Nor will I insult your intelligence by laboriously
explaining the basics – I'll assume that you're already familiar with the straightforward examples covered
in Books Online, and now want to take your knowledge further.
All of the examples used in this book are based on real-life scenarios that I've encountered, and they
show you how to deal with problems that you're likely to face in most typical SQL Server environments. I
promise not to show you seemingly perfect solutions, which you then discover only work in the
artificially-cleansed "AdventureWorks" world; as developers we work with imperfect data, and I'll try to
show you examples that deal with the warts and all. The code examples were tested using the SQL Server
2008 Developer Edition with Service Pack 1 installed, but should work on all editions of SQL Server 2008
unless explicitly stated otherwise.
Finally, I hope that you enjoy reading this book and thinking about the issues discussed. The reason
why I enjoy database development is that it presents a never-ending set of puzzles to solve – and even
when you think you have found the optimum answer to a problem, there is always the possibility of
finding an even better solution in the future. While you shouldn't let this search for perfection detract
you from the job at hand (sometimes, "good enough" is all you need), there are always new techniques
to learn, and alternative methods to explore. I hope that you might learn some of them in the pages that
follow.

C H A P T E R 1

  

Software Development
Methodologies for the
Database World
Databases are software. Therefore, database application development should be treated in the same
manner as any other form of software development. Yet, all too often, the database is thought of as a

concepts that every software developer must know in order to succeed are coupling, cohesion, and
encapsulation:
• Coupling refers to the amount of dependency of one module within a system
upon another module in the same system. It can also refer to the amount of
dependency that exists between different systems. Modules, or systems, are said
to be tightly coupled when they depend on each other to such an extent that a
change in one necessitates a change to the other. This is clearly undesirable, as it
can create a complex (and, sometimes, obscure) network of dependencies
between different modules of the system, so that an apparently simple change in
one module may require identification of and associated changes made to a wide
variety of disparate modules throughout the application. Software developers
should strive instead to produce the opposite: loosely coupled modules and
systems, which can be easily isolated and amended without affecting the rest of
the system.
• Cohesion refers to the degree that a particular module or component provides a
single, well-defined aspect of functionality to the application as a whole. Strongly
cohesive modules, which have only one function, are said to be more desirable
than weakly cohesive modules, which perform many operations and therefore
may be less maintainable and reusable.
• Encapsulation refers to how well the underlying implementation of a module is
hidden from the rest of the system. As you will see, this concept is essentially the
combination of loose coupling and strong cohesion. Logic is said to be
encapsulated within a module if the module’s methods or properties do not
expose design decisions about its internal behaviors.
Unfortunately, these qualitative definitions are somewhat difficult to apply, and in real systems,
there is a significant amount of subjectivity involved in determining whether a given module is or is not
tightly coupled to some other module, whether a routine is cohesive, or whether logic is properly
encapsulated. There is no objective method of measuring these concepts within an application.
Generally, developers will discuss these ideas using comparative terms—for instance, a module may be
said to be less tightly coupled to another module than it was before its interfaces were refactored. But it

string Make;

// Model of the car
string Model;
}
}
This class has three fields: the name of the dealership and address are both strings, but the
collection of the dealership’s cars is typed based on a subclass, Car. In a world without people who are
buying cars, this class works fine—but, unfortunately, the way in which it is modeled forces us to tightly
couple any class that has a car instance to the dealer. Take the owner of a car, for example:
class CarOwner
{
// Name of the car owner
string name;

// The car owner's cars
Dealership.Car[] Cars
}
Notice that the CarOwner’s cars are actually instances of Dealership.Car; in order to own a car, it
seems to be presupposed that there must have been a dealership involved. This doesn’t leave any room
for cars sold directly by their owner—or stolen cars, for that matter! There are a variety of ways of fixing
this kind of coupling, the simplest of which would be to not define Car as a subclass, but rather as its own
stand-alone class. Doing so would mean that a CarOwner would be coupled to a Car, as would a
Dealership—but a CarOwner and a Dealership would not be coupled at all. This makes sense and more
accurately models the real world.
3