Thư viện tri thức trực tuyến
Kho tài liệu với 50,000+ tài liệu học thuật
© 2023 Siêu thị PDF - Kho tài liệu học thuật hàng đầu Việt Nam
Expert SQL Server 2008 Development
Nội dung xem thử
Mô tả chi tiết
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
See last page for details
on $10 eBook version
ISBN 978-1-4302-7213-7
9 781430 272137
5 49 9 9
Expert SQL Server 2008 Development
Dear Reader,
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
is that Expert SQL Server 2008 Development, unlike most books on the subject, is
not intended to provide a comprehensive reference to the features available in
SQL Server 2008. Such information is available in Microsoft Books Online, and
has been repeated in many books already. Instead, my aim is to share knowledge
and skills required to create first-class database applications, which exemplify
best practices in database development.
The topics covered in this book represent interesting, sometimes complex,
and frequently misunderstood facets of database development. Understanding
these areas will set you apart as an expert SQL Server developer. Some of the topics 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 approaches, and give you the information and tools to decide which is most appropriate
for your particular environment.
After reading this book, you will gain an appreciation of areas such as testing and exception handling, to ensure your code is robust, scalable, and easy to
maintain. You’ll learn how to create secure databases by controlling access to sensitive information, and you’ll find out how to encrypt data to protect it from prying 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
concurrent users of a database. Finally, I’ll teach you how to deal with complex
data representing temporal, spatial, and hierarchical information. Together, we’ll
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
2008 Development
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
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@springersbm.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
Contents at a Glance
Contents at a Glance..............................................................................................iv
Contents..................................................................................................................v
About the Author.................................................................................................. xvi
About the Technical Reviewer............................................................................ xvii
Acknowledgments ............................................................................................. xviii
Preface................................................................................................................. xix
Chapter 1: Software Development Methodologies for the Database World............1
Chapter 2: Best Practices for Database Programming.........................................23
Chapter 3: Testing Database Routines..................................................................49
Chapter 4: Errors and Exceptions .........................................................................71
Chapter 5: Privilege and Authorization...............................................................101
Chapter 6: Encryption .........................................................................................121
Chapter 7: SQLCLR: Architecture and Design Considerations.............................159
Chapter 8: Dynamic T-SQL ..................................................................................195
Chapter 9: Designing Systems for Application Concurrency ..............................235
Chapter 10: Working with Spatial Data...............................................................283
Chapter 11: Working with Temporal Data...........................................................321
Chapter 12: Trees, Hierarchies, and Graphs .......................................................371
Index ....................................................................................................................419
iv
Contents
Contents at a Glance..............................................................................................iv
Contents..................................................................................................................v
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
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
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
CONTENTS
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
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
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
Chapter 5: Privilege and Authorization...............................................................101
The Principle of Least Privilege .....................................................................................102
viii
CONTENTS
Creating Proxies in SQL Server............................................................................................................. 103
Server-Level Proxies ....................................................................................................................... 103
Database-Level Proxies................................................................................................................... 104
Data Security in Layers: The Onion Model............................................................................................ 104
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
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
A Simple Example: E-Mail Address Format Validation ......................................................................... 162
SQLCLR Security and Reliability Features .....................................................................163
Security Exceptions .............................................................................................................................. 164
Host Protection Exceptions................................................................................................................... 165
The Quest for Code Safety .................................................................................................................... 168
Selective Privilege Escalation via Assembly References...................................................................... 168
Working with Host Protection Privileges ......................................................................................... 169
Working with Code Access Security Privileges ............................................................................... 173
Granting Cross-Assembly Privileges..................................................................................................... 175
Database Trustworthiness............................................................................................................... 175
Strong Naming................................................................................................................................. 177
Performance Comparison: SQLCLR vs. TSQL.................................................................178
Creating a “Simple Sieve” for Prime Numbers..................................................................................... 179
Calculating Running Aggregates .......................................................................................................... 181
String Manipulation .............................................................................................................................. 183
x
CONTENTS
Enhancing Service Broker Scale-Out with SQLCLR .......................................................185
XML Serialization.................................................................................................................................. 185
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
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
Sharing Resources Between Concurrent Users.............................................................269
Controlling Resource Allocation............................................................................................................ 272
Calculating Effective and Shared Maximum Resource Allocation ........................................................ 277
Controlling Concurrent Request Processing......................................................................................... 279
Summary .......................................................................................................................281
Chapter 10: Working with Spatial Data...............................................................283
Modeling Spatial Data....................................................................................................283
Spatial Reference Systems................................................................................................................... 286
Geographic Coordinate Systems ..................................................................................................... 286
Projected Coordinate Systems ........................................................................................................ 286
Applying Coordinate Systems to the Earth ........................................................................................... 288
Datum.............................................................................................................................................. 288
Prime Meridian ................................................................................................................................ 288
Projection ........................................................................................................................................ 289
Spatial Reference Identifiers ................................................................................................................ 290
xii
CONTENTS
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