Expert SQL Server 2008 Development

454 Pages·2009·9.95 MB·English

CYAN YELLOW
MAGENTA BLACK
PANTONE 123 C
BOOKS FOR PROFESSIONALS BY PROFESSIONALS® THE EXPERT’S VOICE® IN SQL SERVER
Companion
Expert SQL Server 2008 Development eBook Available
Dear Reader, Expert
There are already many books written about SQL Server, so why did I choose to
help write yet another one? What makes this any different to the rest? The answer S
is that Expert SQL Server 2008 Development, unlike most books on the subject, is
Q
not intended to provide a comprehensive reference to the features available in
SQL Server 2008. Such information is available in Microsoft Books Online, and L
Alastair Aitchison, has been repeated in many books already. Instead, my aim is to share knowledge

Author of and skills required to create first-class database applications, which exemplify
S Expert
Beginning Spatial With best practices in database development.
SQL Server 2008 The topics covered in this book represent interesting, sometimes complex, e
and frequently misunderstood facets of database development. Understanding
r
these areas will set you apart as an expert SQL Server developer. Some of the top-
v SQL Server 2008
ics are hotly debated in the software community, and there is not always a single
“best” solution to any given problem. Instead, I’ll show you a variety of approach- e
es, and give you the information and tools to decide which is most appropriate
r
for your particular environment.

After reading this book, you will gain an appreciation of areas such as test- 2
ing and exception handling, to ensure your code is robust, scalable, and easy to
0 Development
maintain. You’ll learn how to create secure databases by controlling access to sen-
sitive information, and you’ll find out how to encrypt data to protect it from pry- 0
ing eyes. You’ll also learn how to create flexible, high-performance applications
using dynamic SQL and SQLCLR, and you’ll discover various models of handling 8
concurrent users of a database. Finally, I’ll teach you how to deal with complex
Adam Machanic,
data representing temporal, spatial, and hierarchical information. Together, we’ll D
Author of
uncover some of the interesting issues that can arise in these situations.
Expert SQL Server 2005 I’ve worked hard on this book, to make it useful to readers of all skill levels. e
Development
Beginner, expert, or in between, you’ll find something of use in this book. My
v
hope is that it helps you become truly an expert SQL Server developer.
e
Advanced SQL Server techniques for
Alastair Aitchison l
Companion eBook o database professionals
p
THE APRESS ROADMAP
m
Pro T-SQL 2008
Beginning T-SQL 2008 Programmer’s Guide
See last page for details Expert e
on $10 eBook version
SQL Server 2008 Development
SQL Server 2008 n
Accelerated SQL Server 2008
Transact-SQL Recipes
t
Alastair Aitchison Adam Machanic
SOURCE CODE ONLINE and
www.apress.com ISBN 978-1-4302-7213-7
Aitchison
54999
Machanic
US $49.99
Shelve in:
Databases / SQL Server
User level: 9 781430 272137
Intermediate / Advanced
this print for content only—size & color not accurate trim = 7.5" x 9.25" spine = 0.84375" 456 page count
CYAN YELLOW
MAGENTA BLACK
PANTONE 123 C
BOOKS FOR PROFESSIONALS BY PROFESSIONALS® THE EXPERT’S VOICE® IN SQL SERVER
Companion
Expert SQL Server 2008 Development eBook Available
Dear Reader, Expert
There are already many books written about SQL Server, so why did I choose to
help write yet another one? What makes this any different to the rest? The answer S
is that Expert SQL Server 2008 Development, unlike most books on the subject, is
Q
not intended to provide a comprehensive reference to the features available in
SQL Server 2008. Such information is available in Microsoft Books Online, and L
Alastair Aitchison, has been repeated in many books already. Instead, my aim is to share knowledge

Author of and skills required to create first-class database applications, which exemplify
S Expert
Beginning Spatial With best practices in database development.
SQL Server 2008 The topics covered in this book represent interesting, sometimes complex, e
and frequently misunderstood facets of database development. Understanding
r
these areas will set you apart as an expert SQL Server developer. Some of the top-
v SQL Server 2008
ics are hotly debated in the software community, and there is not always a single
“best” solution to any given problem. Instead, I’ll show you a variety of approach- e
es, and give you the information and tools to decide which is most appropriate
r
for your particular environment.

After reading this book, you will gain an appreciation of areas such as test- 2
ing and exception handling, to ensure your code is robust, scalable, and easy to
0 Development
maintain. You’ll learn how to create secure databases by controlling access to sen-
sitive information, and you’ll find out how to encrypt data to protect it from pry- 0
ing eyes. You’ll also learn how to create flexible, high-performance applications
using dynamic SQL and SQLCLR, and you’ll discover various models of handling 8
concurrent users of a database. Finally, I’ll teach you how to deal with complex
Adam Machanic,
data representing temporal, spatial, and hierarchical information. Together, we’ll D
Author of
uncover some of the interesting issues that can arise in these situations.
Expert SQL Server 2005 I’ve worked hard on this book, to make it useful to readers of all skill levels. e
Development
Beginner, expert, or in between, you’ll find something of use in this book. My
v
hope is that it helps you become truly an expert SQL Server developer.
e
Advanced SQL Server techniques for
Alastair Aitchison l
Companion eBook o database professionals
p
THE APRESS ROADMAP
m
Pro T-SQL 2008
Beginning T-SQL 2008 Programmer’s Guide
See last page for details Expert e
on $10 eBook version
SQL Server 2008 Development
SQL Server 2008 n
Accelerated SQL Server 2008
Transact-SQL Recipes
t
Alastair Aitchison Adam Machanic
SOURCE CODE ONLINE and
www.apress.com ISBN 978-1-4302-7213-7
Aitchison
54999
Machanic
US $49.99
Shelve in:
Databases / SQL Server
User level: 9 781430 272137
Intermediate / Advanced
this print for content only—size & color not accurate trim = 7.5" x 9.25" spine = 0.84375" 456 page count
Expert SQL Server
2008 Development

(cid:132) (cid:132) (cid:132)
Alastair Aitchison
Adam Machanic
Expert SQL Server 2008 Development
Copyright © 2009 by Alastair Aitchison and Adam Machanic
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information storage or
retrieval system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-4302-7213-7
ISBN-13 (electronic): 978-1-4302-7212-0
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of
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
For Clare and Douglas
(cid:132) CONTENTS
Contents at a Glance
(cid:132) Contents at a Glance..............................................................................................iv
(cid:132) Contents..................................................................................................................v
(cid:132) About the Author..................................................................................................xvi
(cid:132) About the Technical Reviewer............................................................................xvii
(cid:132) Acknowledgments.............................................................................................xviii
(cid:132) Preface.................................................................................................................xix
(cid:132) Chapter 1: Software Development Methodologies for the Database World............1
(cid:132) Chapter 2: Best Practices for Database Programming.........................................23
(cid:132) Chapter 3: Testing Database Routines..................................................................49
(cid:132) Chapter 4: Errors and Exceptions.........................................................................71
(cid:132) Chapter 5: Privilege and Authorization...............................................................101
(cid:132) Chapter 6: Encryption.........................................................................................121
(cid:132) Chapter 7: SQLCLR: Architecture and Design Considerations.............................159
(cid:132) Chapter 8: Dynamic T-SQL..................................................................................195
(cid:132) Chapter 9: Designing Systems for Application Concurrency..............................235
(cid:132) Chapter 10: Working with Spatial Data...............................................................283
(cid:132) Chapter 11: Working with Temporal Data...........................................................321
(cid:132) Chapter 12: Trees, Hierarchies, and Graphs.......................................................371
(cid:132)Index....................................................................................................................419
iv
Contents
(cid:132) Contents at a Glance..............................................................................................iv
(cid:132) Contents..................................................................................................................v
(cid:132) About the Author..................................................................................................xvi
(cid:132) About the Technical Reviewer............................................................................xvii
(cid:132) Acknowledgments.............................................................................................xviii
(cid:132) Preface.................................................................................................................xix
(cid:132) 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
(cid:132) 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
(cid:132) 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
Best Practice SQL Programming Techniques..................................................................28
Identify Hidden Assumptions in Your Code.............................................................................................29
Don’t Take Shortcuts..............................................................................................................................33
Testing....................................................................................................................................................36
Code Review...........................................................................................................................................39
Validate All Input.....................................................................................................................................40
Future-proof Your Code..........................................................................................................................42
Limit Your Exposure................................................................................................................................43
Exercise Good Coding Etiquette..............................................................................................................43
Comments.........................................................................................................................................44
Indentations and Statement Blocks...................................................................................................45
If All Else Fails. . ....................................................................................................................................46
Creating a Healthy Development Environment................................................................46
Summary.........................................................................................................................47
vi
(cid:132) CONTENTS
(cid:132) Chapter 3: Testing Database Routines..................................................................49
Approaches to Testing.....................................................................................................49
Unit and Functional Testing.............................................................................................50
Unit Testing Frameworks.......................................................................................................................52
Regression Testing.................................................................................................................................55
Guidelines for Implementing Database Testing Processes and Procedures....................55
Why Is Testing Important?......................................................................................................................56
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
(cid:132) 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
(cid:132) 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
Why Handle Exceptions in T-SQL?..........................................................................................................86
Exception “Handling” Using @@ERROR.................................................................................................86
SQL Server’s TRY/CATCH Syntax............................................................................................................87
Getting Extended Error Information in the Catch Block.....................................................................89
Rethrowing Exceptions......................................................................................................................90
When Should TRY/CATCH Be Used?..................................................................................................91
Using TRY/CATCH to Build Retry Logic..............................................................................................91
Exception Handling and SQLCLR............................................................................................................93
Transactions and Exceptions...........................................................................................96
The Myths of Transaction Abortion.........................................................................................................96
XACT_ABORT: Turning Myth into (Semi-)Reality....................................................................................98
TRY/CATCH and Doomed Transactions...................................................................................................99
Summary.......................................................................................................................100
(cid:132) Chapter 5: Privilege and Authorization...............................................................101
The Principle of Least Privilege.....................................................................................102
viii