Expert SQL Server 2005 Development

by Machanic, Adam

• ISBN13: 9781590597293
• ISBN10: 159059729X
• Format: Paperback
• Copyright: 2007-05-21
• Publisher: Springer-Verlag New York Inc

Summary

While building on the skills you already have, Expert SQL Server 2005 Development will help you become an even better developer by focusing on best practices and demonstrating how to design highperformance, maintainable database applications.This book starts by reintroducing the database as a integral part of the software development ecosystem. Yours"ll learn how to think about SQL Server development as you would any other software development. For example, there's no reason you canrs"t architect and test database routines just as you would architect and test application code. And nothing should stop you from implementing the types of exception handling and security rules that are considered so important in other tiers, even if they are usually ignored in the database.Yours"ll learn how to apply development methodologies like these to produce highquality encryption and SQLCLR solutions. Furthermore, yours"ll discover how to exploit a variety of tools that SQL Server offers in order to properly use dynamic SQL and to improve concurrency in your applications. Finally, yours"ll become well versed in implementing spatial and temporal database designs, as well as approaching graph and hierarchy problems.

Table of Contents

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.