Expert SQL Server 2005 Development

Foreword     xiii
About the Authors     xv
About the Technical Reviewer     xvii
Acknowledgments     xix
Introduction     xxi
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
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
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 Weeded?     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
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
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
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
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
Extending User-Defined Aggregates     162
Summary     167
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
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
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
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
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
Index     439