Expert SQL Server 2008 Development

Cover......Page 1
Contents at a Glance......Page 6
Contents......Page 7
About the Author......Page 18
About the Technical Reviewer......Page 19
Acknowledgments......Page 20
Preface......Page 21
Architecture Revisited......Page 23
Coupling......Page 25
Cohesion......Page 26
Interfaces......Page 27
Interface Design......Page 28
Integrating Databases and Object-Oriented Systems......Page 30
Data Logic......Page 32
Business Logic......Page 33
The “Object-Relational Impedance Mismatch”......Page 34
Are Tables Really Classes in Disguise?......Page 35
Modeling Inheritance......Page 36
ORM: A Solution That Creates Many Problems......Page 39
Introducing the Database-As-API Mindset......Page 40
Performance......Page 41
Maintainability......Page 42
Allowing for Future Requirements......Page 43
Summary......Page 44
Defensive Programming......Page 45
Attitudes to Defensive Programming......Page 46
Why Use a Defensive Approach to Database Development?......Page 49
Best Practice SQL Programming Techniques......Page 50
Identify Hidden Assumptions in Your Code......Page 51
Don’t Take Shortcuts......Page 55
Testing......Page 58
Code Review......Page 61
Validate All Input......Page 62
Future-proof Your Code......Page 64
Exercise Good Coding Etiquette......Page 65
Comments......Page 66
Indentations and Statement Blocks......Page 67
If All Else Fails. . .......Page 68
Summary......Page 69
Approaches to Testing......Page 71
Unit and Functional Testing......Page 72
Unit Testing Frameworks......Page 74
Regression Testing......Page 76
Guidelines for Implementing Database Testing Processes and Procedures......Page 77
What Kind of Testing Is Important?......Page 78
Will Management Buy In?......Page 79
Performance Monitoring Tools......Page 80
Real-Time Client-Side Monitoring......Page 81
Server-Side Traces......Page 82
System Monitoring......Page 83
Dynamic Management Views (DMVs)......Page 84
Extended Events......Page 85
Data Collector......Page 87
Capturing Baseline Metrics......Page 89
Granular Analysis......Page 90
Summary......Page 92
Exceptions vs. Errors......Page 93
How Exceptions Work in SQL Server......Page 94
Batch-Level Exceptions......Page 95
Parsing and Scope-Resolution Exceptions......Page 97
Connection and Server-Level Exceptions......Page 98
The XACT_ABORT Setting......Page 99
Error Number......Page 100
Error State......Page 101
Additional Information......Page 102
SQL Server’s RAISERROR Function......Page 103
Formatting Error Messages......Page 104
Creating Persistent Custom Error Messages......Page 105
Exception Handling......Page 107
Exception “Handling” Using @@ERROR......Page 108
SQL Server’s TRY/CATCH Syntax......Page 109
Getting Extended Error Information in the Catch Block......Page 111
Rethrowing Exceptions......Page 112
Using TRY/CATCH to Build Retry Logic......Page 113
Exception Handling and SQLCLR......Page 115
The Myths of Transaction Abortion......Page 118
XACT_ABORT: Turning Myth into (Semi-)Reality......Page 120
TRY/CATCH and Doomed Transactions......Page 121
Summary......Page 122
Privilege and Authorization......Page 123
The Principle of Least Privilege......Page 124
Server-Level Proxies......Page 125
Data Security in Layers: The Onion Model......Page 126
Data Organization Using Schemas......Page 127
Basic Impersonation Using EXECUTE AS......Page 129
Ownership Chaining......Page 132
Stored Procedures and EXECUTE AS......Page 134
Stored Procedure Signing Using Certificates......Page 136
Assigning Server-Level Permissions......Page 139
Summary......Page 141
What Should Be Protected?......Page 143
What Are You Protecting Against?......Page 144
The Automatic Key Management Hierarchy......Page 145
Symmetric Keys, Asymmetric Keys, and Certificates......Page 146
Alternative Encryption Management Structures......Page 147
Removing Keys from the Automatic Encryption Hierarchy......Page 148
Extensible Key Management......Page 149
Data Protection and Encryption Methods......Page 150
Hashing......Page 151
Symmetric Key Encryption......Page 152
Asymmetric Key Encryption......Page 156
Transparent Data Encryption......Page 158
Balancing Performance and Security......Page 161
Implications of Encryption on Query Design......Page 167
Equality Matching Using Hashed Message Authentication Codes......Page 170
Wildcard Searches Using HMAC Substrings......Page 175
Range Searches......Page 179
Summary......Page 180
SQLCLR: Architecture and Design Considerations......Page 181
Bridging the SQL/CLR Gap: The SqlTypes Library......Page 182
One Reasonable Solution......Page 183
A Simple Example: E-Mail Address Format Validation......Page 184
SQLCLR Security and Reliability Features......Page 185
Security Exceptions......Page 186
Host Protection Exceptions......Page 187
Selective Privilege Escalation via Assembly References......Page 190
Working with Host Protection Privileges......Page 191
Working with Code Access Security Privileges......Page 195
Database Trustworthiness......Page 197
Strong Naming......Page 199
Performance Comparison: SQLCLR vs. TSQL......Page 200
Creating a “Simple Sieve” for Prime Numbers......Page 201
Calculating Running Aggregates......Page 203
String Manipulation......Page 205
XML Serialization......Page 207
XML Deserialization......Page 208
Binary Serialization with SQLCLR......Page 209
Binary Deserialization......Page 213
Summary......Page 216
Dynamic T-SQL......Page 217
The Stored Procedure vs. Ad Hoc SQL Debate......Page 218
Why Go Dynamic?......Page 219
Compilation and Parameterization......Page 220
Auto-Parameterization......Page 222
Application-Level Parameterization......Page 224
Performance Implications of Parameterization and Caching......Page 225
Supporting Optional Parameters......Page 227
Optional Parameters via Static T-SQL......Page 228
Going Dynamic: Using EXECUTE......Page 234
SQL Injection......Page 240
sp_executesql: A Better EXECUTE......Page 242
Performance Comparison......Page 245
Interface Rules......Page 252
Summary......Page 254
Designing Systems for Application Concurrency......Page 255
The Business Side: What Should Happen When Processes Collide?......Page 256
Isolation Levels and Transactional Behavior......Page 257
REPEATABLE READ Isolation......Page 259
SERIALIZABLE Isolation......Page 260
READ UNCOMMITTED Isolation......Page 261
From Isolation to Concurrency Control......Page 262
Preparing for the Worst: Pessimistic Concurrency......Page 263
Progressing to a Solution......Page 264
Enforcing Pessimistic Locks at Write Time......Page 269
Application Locks: Generalizing Pessimistic Concurrency......Page 270
Hoping for the Best: Optimistic Concurrency......Page 280
Embracing Conflict: Multivalue Concurrency Control......Page 286
Sharing Resources Between Concurrent Users......Page 289
Controlling Resource Allocation......Page 292
Calculating Effective and Shared Maximum Resource Allocation......Page 297
Controlling Concurrent Request Processing......Page 299
Summary......Page 301
Modeling Spatial Data......Page 303
Projected Coordinate Systems......Page 306
Prime Meridian......Page 308
Projection......Page 309
Spatial Reference Identifiers......Page 310
Geography vs. Geometry......Page 312
Standards Compliance......Page 313
Technical Limitations and Performance......Page 314
Well-Known Text......Page 316
Well-Known Binary......Page 317
Importing Data......Page 318
Querying Spatial Data......Page 322
Nearest-Neighbor Queries......Page 324
Finding Locations Within a Given Bounding Box......Page 328
How Does a Spatial Index Work?......Page 333
Optimizing the Grid......Page 335
Summary......Page 339
Modeling Time-Based Information......Page 341
SQL Server’s Date/Time Data Types......Page 342
Input Date Formats......Page 343
Output Date Formatting......Page 345
Efficiently Querying Date/Time Columns......Page 346
Date/Time Calculations......Page 349
Truncating the Time Portion of a datetime Value......Page 350
Finding Relative Dates......Page 352
How Many Candles on the Birthday Cake?......Page 355
Defining Periods Using Calendar Tables......Page 356
Dealing with Time Zones......Page 361
Storing UTC Time......Page 363
Using the datetimeoffset Type......Page 364
Working with Intervals......Page 366
Modeling and Querying Continuous Intervals......Page 367
Modeling and Querying Independent Intervals......Page 374
Overlapping Intervals......Page 378
Time Slicing......Page 382
Modeling Durations......Page 385
Managing Bitemporal Data......Page 386
Summary......Page 390
Terminology: Everything Is a Graph......Page 391
The Basics: Adjacency Lists and Graphs......Page 393
Constraining the Edges......Page 394
Basic Graph Queries: Who Am I Connected To?......Page 396
Traversing the Graph......Page 398
Adjacency List Hierarchies......Page 408
Finding Direct Descendants......Page 409
Traversing down the Hierarchy......Page 411
Ordering the Output......Page 412
Are CTEs the Best Choice?......Page 416
Traversing up the Hierarchy......Page 420
Inserting New Nodes and Relocating Subtrees......Page 421
Constraining the Hierarchy......Page 422
Persisted Materialized Paths......Page 425
Finding Subordinates......Page 426
Navigating up the Hierarchy......Page 427
Inserting Nodes......Page 428
Relocating Subtrees......Page 430
Deleting Nodes......Page 431
The hierarchyid Datatype......Page 432
Finding Subordinates......Page 433
Navigating up the Hierarchy......Page 434
Inserting Nodes......Page 435
Relocating Subtrees......Page 436
Constraining the Hierarchy......Page 437
Summary......Page 438
A......Page 439
B......Page 440
D......Page 441
E......Page 442
H G......Page 444
K......Page 445
N......Page 446
O......Page 447
R......Page 448
S......Page 449
T......Page 451
U W......Page 452
Y......Page 453