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