2007 by Adam Machanic, Hugo Kornelis, Lara Rubbelke
Contents
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
¦CHAPTER 1 Software Development Methodologies for
the Database World . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Architecture Revisited. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Coupling, Cohesion, and Encapsulation . . . . . . . . . . . . . . . . . . . . . . . . . 2
Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
The Central Problem: Integrating Databases and
Object-Oriented Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Where Should the Logic Go? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
The Object-Relational Impedance Mismatch . . . . . . . . . . . . . . . . . . . 12
ORM: A Solution That Creates Many Problems. . . . . . . . . . . . . . . . . . . . . . . 17
Introducing the Database-as-API Mindset . . . . . . . . . . . . . . . . . . . . . . . . . . 18
The Great Balancing Act. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Testability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Maintainability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Creeping Featurism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
¦CHAPTER 2 Testing Database Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Introduction to Black Box and White Box Testing. . . . . . . . . . . . . . . . . . . . . 23
Unit and Functional Testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Unit Testing Frameworks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
The Importance of Regression Testing . . . . . . . . . . . . . . . . . . . . . . . . . 29
vii
Guidelines for Implementing Database Testing Processes
and Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Why Is Testing Important? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
What Kind of Testing Is Important? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
How Many Tests Are Needed? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Will Management Buy In?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Performance Testing and Profiling Database Systems . . . . . . . . . . . . . . . . 33
Capturing Baseline Metrics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Profiling Using Traces and SQL Server Profiler . . . . . . . . . . . . . . . . . . 34
Evaluating Performance Counters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Big-Picture Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Granular Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Fixing Problems: Is Focusing on the Obvious Issues Enough?. . . . . 40
Introducing the SQLQueryStress Performance Testing Tool . . . . . . . . . . . . 40
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
¦CHAPTER 3 Errors and Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Exceptions vs. Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
How Exceptions Work in SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Statement-Level Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Batch-Level Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Parsing and Scope-Resolution Exceptions . . . . . . . . . . . . . . . . . . . . . 50
Connection and Server-Level Exceptions . . . . . . . . . . . . . . . . . . . . . . 52
The XACT_ABORT Setting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Dissecting an Error Message. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
SQL Server’s RAISERROR Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Monitoring Exception Events with Traces . . . . . . . . . . . . . . . . . . . . . . 60
Exception Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Why Handle Exceptions in T-SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Exception “Handling” Using @@ERROR . . . . . . . . . . . . . . . . . . . . . . . 61
SQL Server’s TRY/CATCH Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Transactions and Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
The Myths of Transaction Abortion . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
XACT_ABORT: Turning Myth into (Semi-)Reality . . . . . . . . . . . . . . . . . 69
TRY/CATCH and Doomed Transactions. . . . . . . . . . . . . . . . . . . . . . . . . 71
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
viii ¦CONTENTS
¦CHAPTER 4 Privilege and Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
The Principle of Least Privilege. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Creating Proxies in SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Data Security in Layers: The Onion Model. . . . . . . . . . . . . . . . . . . . . . 75
Data Organization Using Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Basic Impersonation Using EXECUTE AS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Ownership Chaining . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Privilege Escalation Without Ownership Chains . . . . . . . . . . . . . . . . . . . . . . 83
Stored Procedures and EXECUTE AS . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Stored Procedure Signing Using Certificates . . . . . . . . . . . . . . . . . . . 85
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
¦CHAPTER 5 Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
What to Protect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Encryption Terminology: What You Need to Know . . . . . . . . . . . . . . . . . . . . 93
SQL Server 2005 Encryption Key Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . 94
Service Master Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Database Master Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
SQL Server 2005 Data Protection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
HashBytes(). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Asymmetric Key and Certificate Encryption . . . . . . . . . . . . . . . . . . . . 98
Symmetric Key Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
EncryptByPassphrase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Securing Data from the DBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Architecting for Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Setting Up the Solution and Defining the Problem. . . . . . . . . . . . . . 112
Searching Encrypted Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
¦CHAPTER 6 SQLCLR: Architecture and Design Considerations. . . . . . . 133
Bridging the SQL/CLR Gap: the SqlTypes Library. . . . . . . . . . . . . . . . . . . . 134
Wrapping Code to Promote Cross-Tier Reuse . . . . . . . . . . . . . . . . . . . . . . 135
A Simple Example: E-Mail Address Format Validation . . . . . . . . . . . 135
SQLCLR Security and Reliability Features . . . . . . . . . . . . . . . . . . . . . . . . . . 137
The Quest for Code Safety . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Selective Privilege Escalation via Assembly References. . . . . . . . . 141
Granting Cross-Assembly Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . 148
Enhancing Service Broker Scale-Out with SQLCLR. . . . . . . . . . . . . . . . . . 151
¦CONTENTS ix
Extending User-Defined Aggregates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
¦CHAPTER 7 Dynamic T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Dynamic T-SQL vs. Ad Hoc T-SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
The Stored Procedure vs. Ad Hoc SQL Debate . . . . . . . . . . . . . . . . . 170
Why Go Dynamic? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Compilation and Parameterization . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Auto-Parameterization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Application-Level Parameterization . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Performance Implications of Parameterization and Caching . . . . . 177
Supporting Optional Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Optional Parameters via Static T-SQL . . . . . . . . . . . . . . . . . . . . . . . . 180
Going Dynamic: Using EXECUTE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
SQL Injection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
sp_executesql: A Better EXECUTE. . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Dynamic SQL Security Considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Permissions to Referenced Objects . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Interface Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
¦CHAPTER 8 Designing Systems for Application Concurrency . . . . . . . . 209
The Business Side: What Should Happen When
Processes Collide? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
A Brief Overview of SQL Server Isolation Levels . . . . . . . . . . . . . . . 211
Concurrency Control and SQL Server’s Native
Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Preparing for the Worst: Pessimistic Concurrency. . . . . . . . . . . . . . . . . . . 217
Enforcing Pessimistic Locks at Write Time . . . . . . . . . . . . . . . . . . . . 222
Application Locks: Generalizing Pessimistic Concurrency . . . . . . . 224
Hoping for the Best: Optimistic Concurrency . . . . . . . . . . . . . . . . . . . . . . . 234
Embracing Conflict: Multivalue Concurrency . . . . . . . . . . . . . . . . . . . . . . . 239
Extending Scalability Through Queuing . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
x ¦CONTENTS
¦CHAPTER 9 Working with Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Representing Geospatial Data by Latitude and Longitude . . . . . . . . . . . . 251
Setting Up Sample Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Calculating the Distance Between Two Points . . . . . . . . . . . . . . . . . 254
Moving from Point to Point . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Searching the Neighborhood . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
The Bounding Box . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Finding the Nearest Neighbor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
The Dynamic Bounding Box. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Representing Geospatial Data by Using the Hierarchical
Triangular Mesh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294
A Simplified Description of HTM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294
Implementing the HtmID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Functions in the Spatial Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Other Types of Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Three-Dimensional Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Astronomical Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Virtual Space. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Representing Regions As Polygons. . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
¦CHAPTER 10 Working with Temporal Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Representing More Than Just Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
SQL Server’s Date/Time Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Input Date Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Output Date Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Efficiently Querying Date/Time Columns . . . . . . . . . . . . . . . . . . . . . . 320
Date/Time Calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
Defining Periods Using Calendar Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Designing and Querying Temporal Data Stores . . . . . . . . . . . . . . . . . . . . . 340
Dealing with Time Zones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Working with Intervals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Modeling Durations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Managing Bitemporal Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
¦CONTENTS xi
¦CHAPTER 11 Trees, Hierarchies, and Graphs. . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Terminology: Everything Is a Graph . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
The Basics: Adjacency Lists and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
Constraining the Edges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Basic Graph Queries: Who Am I Connected To? . . . . . . . . . . . . . . . . 380
Traversing the Graph. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Adjacency List Hierarchies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Querying Adjacency List Hierarchies: The Basics. . . . . . . . . . . . . . . 392
Finding Direct Descendants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
Traversing down the Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
Traversing up the Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Inserting New Nodes and Relocating Subtrees. . . . . . . . . . . . . . . . . 405
Deleting Existing Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
Constraining the Hierarchy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Persisting Materialized Paths . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Finding Subordinates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Navigating up the Hierarchy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
Optimizing the Materialized Path Solution. . . . . . . . . . . . . . . . . . . . . 413
Inserting Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
Relocating Subtrees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Deleting Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Constraining the Hierarchy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Nested Sets Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Finding Subordinates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
Navigating up the Hierarchy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
Inserting Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
Relocating Subtrees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
Deleting Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
Constraining the Hierarchy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
Цена: 200руб.