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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.


the trademark owner, with no intention of infringement of the trademark.
President and Publisher: Paul Manning
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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

 About the Author.................................................................................................. xvi
 About the Technical Reviewer ............................................................................ xvii
 Acknowledgments ............................................................................................. xviii
 Preface................................................................................................................. xix
 Chapter 1: Software Development Methodologies for the Database World ............1
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 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

What Kind of Testing Is Important? ........................................................................................................ 56
How Many Tests Are Needed?................................................................................................................ 57
Will Management Buy In?....................................................................................................................... 58
Performance Monitoring Tools ........................................................................................58
Real-Time Client-Side Monitoring .......................................................................................................... 59
Server-Side Traces................................................................................................................................. 60
System Monitoring ................................................................................................................................. 61
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 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

Data Organization Using Schemas.................................................................................105
Basic Impersonation Using EXECUTE AS .......................................................................107
Ownership Chaining.......................................................................................................110
Privilege Escalation Without Ownership Chains ............................................................112
Stored Procedures and EXECUTE AS .................................................................................................... 112
Stored Procedure Signing Using Certificates ....................................................................................... 114
Assigning Server-Level Permissions .................................................................................................... 117
Summary .......................................................................................................................119
 Chapter 6: Encryption .........................................................................................121
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 CONTENTS
Asymmetric Key Encryption.................................................................................................................. 134
Transparent Data Encryption................................................................................................................ 136
Balancing Performance and Security ............................................................................139

XML Deserialization.............................................................................................................................. 186
Binary Serialization with SQLCLR ......................................................................................................... 187
Binary Deserialization........................................................................................................................... 191
Summary .......................................................................................................................194
 Chapter 8: Dynamic T-SQL ..................................................................................195
Dynamic T-SQL vs. Ad Hoc T-SQL..................................................................................196
The Stored Procedure vs. Ad Hoc SQL Debate...............................................................196
Why Go Dynamic?..........................................................................................................197
Compilation and Parameterization ....................................................................................................... 198
Auto-Parameterization.......................................................................................................................... 200
Application-Level Parameterization...................................................................................................... 202
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 CONTENTS
READ COMMITTED Isolation............................................................................................................. 239

Geography vs. Geometry................................................................................................292
Standards Compliance ......................................................................................................................... 293
Accuracy............................................................................................................................................... 294
Technical Limitations and Performance ............................................................................................... 294
Creating Spatial Data.....................................................................................................296
Well-Known Text .................................................................................................................................. 296
Well-Known Binary ............................................................................................................................... 297
Geography Markup Language............................................................................................................... 298
Importing Data...................................................................................................................................... 298
Querying Spatial Data ....................................................................................................302
Nearest-Neighbor Queries.................................................................................................................... 304
Finding Locations Within a Given Bounding Box .................................................................................. 308
Spatial Indexing .............................................................................................................313
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 CONTENTS
xv
Inserting Nodes .................................................................................................................................... 408
Relocating Subtrees ............................................................................................................................. 409
Deleting Nodes ..................................................................................................................................... 411
Constraining the Hierarchy................................................................................................................... 411
The hierarchyid Datatype...............................................................................................412
Finding Subordinates............................................................................................................................ 413
Navigating up the Hierarchy ................................................................................................................. 414
Inserting Nodes .................................................................................................................................... 415
Relocating Subtrees ............................................................................................................................. 416
Deleting Nodes ..................................................................................................................................... 417
Constraining the Hierarchy................................................................................................................... 417
Summary .......................................................................................................................418
Index ................................................................................................................419

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

I thank them for their tolerance, patience, and support. I couldn’t do anything without them.
And thankyou to you, the reader, for purchasing this book. I hope that you find the content
interesting, useful, and above all, enjoyable to read.
xviii
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.