Expert SQL Server 2008 Development (eBook)

Alastair Aitchison has more than eight years of experience as a management information consultant, specializing in the design and deployment of online reporting systems. For the last three years, he has been employed as a reporting and analysis manager at Aviva, the world's fifth largest insurance group. In this role, he has championed the use of spatial data in corporate applications including the geographic analysis of risk patterns, plotting the success of regional marketing campaigns, and understanding the impact of major weather incidents. Alastair is a Microsoft Office Specialist Master Instructor and has delivered numerous training courses to individuals and small groups on a range of software packages.

Contents at a Glance 5
Table of contents 6
About the Author 17
About the Technical Reviewer 18
Acknowledgments 19
Preface 20
CHAPTER 1 Software Development Methodologies for the Database World 21
Architecture Revisited 21
Coupling 23
Cohesion 24
Encapsulation 25
Interfaces 25
Interfaces As Contracts 26
Interface Design 26
Integrating Databases and Object-Oriented Systems 28
Data Logic 30
Business Logic 31
Application Logic 32
The “Object-Relational Impedance Mismatch” 32
Are Tables Really Classes in Disguise? 33
Modeling Inheritance 34
ORM: A Solution That Creates Many Problems 37
Introducing the Database-As-API Mindset 38
The Great Balancing Act 39
Performance 39
Testability 40
Maintainability 40
Security 41
Allowing for Future Requirements 41
Summary 42
CHAPTER 2 Best Practices for Database Programming 43
Defensive Programming 43
Attitudes to Defensive Programming 44
Why Use a Defensive Approach to Database Development? 47
Best Practice SQL Programming Techniques 48
Identify Hidden Assumptions in Your Code 49
Don’t Take Shortcuts 53
Testing 56
Code Review 59
Validate All Input 60
Future-proof Your Code 62
Limit Your Exposure 63
Exercise Good Coding Etiquette 63
Comments 64
Indentations and Statement Blocks 65
If All Else Fails. . . 66
Creating a Healthy Development Environment 67
Summary 67
CHAPTER 3 Testing Database Routines 69
Approaches to Testing 69
Unit and Functional Testing 70
Unit Testing Frameworks 72
Regression Testing 74
Guidelines for Implementing Database Testing Processes and Procedures 75
Why Is Testing Important? 76
What Kind of Testing Is Important? 76
How Many Tests Are Needed? 77
Will Management Buy In? 77
Performance Monitoring Tools 78
Real-Time Client-Side Monitoring 79
Server-Side Traces 80
System Monitoring 81
Dynamic Management Views (DMVs) 82
Extended Events 83
Data Collector 85
Analyzing Performance Data 87
Capturing Baseline Metrics 87
Big-Picture Analysis 88
Untitled 88
Fixing Problems: Is It Sufficient to Focus on the Obvious? 90
Summary 90
CHAPTER 4 Errors and Exceptions 91
Exceptions vs. Errors 91
How Exceptions Work in SQL Server 92
Statement-Level Exceptions 93
Batch-Level Exceptions 93
Parsing and Scope-Resolution Exceptions 95
Connection and Server-Level Exceptions 96
The XACT_ABORT Setting 97
Dissecting an Error Message 98
Error Number 98
Error Level 99
Error State 99
Additional Information 100
SQL Server’s RAISERROR Function 101
Formatting Error Messages 102
Creating Persistent Custom Error Messages 103
Logging User-Thrown Exceptions 105
Monitoring Exception Events with Traces 105
Exception Handling 105
Why Handle Exceptions in T-SQL? 106
Exception “Handling” Using @@ERROR 106
SQL Server’s TRY/CATCH Syntax 107
Getting Extended Error Information in the Catch Block 109
Rethrowing Exceptions 110
When Should TRY/CATCH Be Used? 111
Using TRY/CATCH to Build Retry Logic 111
Exception Handling and SQLCLR 113
Transactions and Exceptions 116
The Myths of Transaction Abortion 116
XACT_ABORT: Turning Myth into (Semi-)Reality 118
TRY/CATCH and Doomed Transactions 119
Summary 120
CHAPTER 5 Privilege and Authorization 121
The Principle of Least Privilege 122
Creating Proxies in SQL Server 123
Server-Level Proxies 123
Database-Level Proxies 124
Data Security in Layers: The Onion Model 124
Data Organization Using Schemas 125
Basic Impersonation Using EXECUTE AS 127
Ownership Chaining 130
Privilege Escalation Without Ownership Chains 132
Stored Procedures and EXECUTE AS 132
Stored Procedure Signing Using Certificates 134
Assigning Server-Level Permissions 137
Summary 139
CHAPTER 6 Encryption 140
Do You Really Need Encryption? 140
What Should Be Protected? 140
What Are You Protecting Against? 141
SQL Server 2008 Encryption Key Hierarchy 142
The Automatic Key Management Hierarchy 142
Symmetric Keys, Asymmetric Keys, and Certificates 143
Database Master Key 144
Service Master Key 144
Alternative Encryption Management Structures 144
Symmetric Key Layering and Rotation 145
Removing Keys from the Automatic Encryption Hierarchy 145
Extensible Key Management 146
Data Protection and Encryption Methods 147
Hashing 148
Symmetric Key Encryption 149
Asymmetric Key Encryption 153
Transparent Data Encryption 155
Balancing Performance and Security 158
Implications of Encryption on Query Design 164
Equality Matching Using Hashed Message Authentication Codes 167
Wildcard Searches Using HMAC Substrings 172
Range Searches 176
Summary 177
CHAPTER 7 SQLCLR: Architecture and Design Considerations 178
Bridging the SQL/CLR Gap: The SqlTypes Library 179
Wrapping Code to Promote Cross-Tier Reuse 180
The Problem 180
One Reasonable Solution 180
A Simple Example: E-Mail Address Format Validation 181
SQLCLR Security and Reliability Features 182
Security Exceptions 183
Host Protection Exceptions 184
The Quest for Code Safety 187
Selective Privilege Escalation via Assembly References 187
Working with Host Protection Privileges 188
Working with Code Access Security Privileges 192
Granting Cross-Assembly Privileges 194
Database Trustworthiness 194
Strong Naming 196
Performance Comparison: SQLCLR vs. TSQL 197
Creating a “Simple Sieve” for Prime Numbers 198
Calculating Running Aggregates 200
String Manipulation 202
Enhancing Service Broker Scale-Out with SQLCLR 204
XML Serialization 204
XML Deserialization 205
Binary Serialization with SQLCLR 206
Binary Deserialization 210
194Summary 213
CHAPTER 8 Dynamic T-SQL 214
Dynamic T-SQL vs. Ad Hoc T-SQL 215
The Stored Procedure vs. Ad Hoc SQL Debate 215
Why Go Dynamic? 216
Compilation and Parameterization 217
Auto-Parameterization 219
Application-Level Parameterization 221
Performance Implications of Parameterization and Caching 222
Supporting Optional Parameters 224
Optional Parameters via Static T-SQL 225
Going Dynamic: Using EXECUTE 231
SQL Injection 237
sp_executesql: A Better EXECUTE 239
Performance Comparison 242
Dynamic SQL Security Considerations 249
Permissions to Referenced Objects 249
Interface Rules 249
Summary 251
CHAPTER 9 Designing Systems for Application Concurrency 252
The Business Side: What Should Happen When Processes Collide? 253
Isolation Levels and Transactional Behavior 254
Blocking Isolation Levels 256
READ COMMITTED Isolation 256
REPEATABLE READ Isolation 256
SERIALIZABLE Isolation 257
Nonblocking Isolation Levels 258
READ UNCOMMITTED Isolation 258
SNAPSHOT Isolation 259
From Isolation to Concurrency Control 259
Preparing for the Worst: Pessimistic Concurrency 260
Progressing to a Solution 261
Enforcing Pessimistic Locks at Write Time 266
Application Locks: Generalizing Pessimistic Concurrency 267
Hoping for the Best: Optimistic Concurrency 277
Embracing Conflict: Multivalue Concurrency Control 283
Sharing Resources Between Concurrent Users 286
Controlling Resource Allocation 289
Calculating Effective and Shared Maximum Resource Allocation 294
Controlling Concurrent Request Processing 296
Summary 298
CHAPTER 10 Working with Spatial Data 299
Modeling Spatial Data 299
Spatial Reference Systems 302
Geographic Coordinate Systems 302
Projected Coordinate Systems 302
Applying Coordinate Systems to the Earth 304
Datum 304
Prime Meridian 304
Projection 305
Spatial Reference Identifiers 306
Geography vs. Geometry 308
Standards Compliance 309
Accuracy 310
Technical Limitations and Performance 310
Creating Spatial Data 312
Well-Known Text 312
Well-Known Binary 313
Geography Markup Language 314
Importing Data 314
Querying Spatial Data 318
Nearest-Neighbor Queries 320
Finding Locations Within a Given Bounding Box 324
Spatial Indexing 329
How Does a Spatial Index Work? 329
Optimizing the Grid 331
Summary 335
CHAPTER 11 Working with Temporal Data 336
Modeling Time-Based Information 336
SQL Server’s Date/Time Data Types 337
Input Date Formats 338
Output Date Formatting 340
Efficiently Querying Date/Time Columns 341
Date/Time Calculations 344
Truncating the Time Portion of a datetime Value 345
Finding Relative Dates 347
How Many Candles on the Birthday Cake? 350
Defining Periods Using Calendar Tables 351
Dealing with Time Zones 356
Storing UTC Time 358
Using the datetimeoffset Type 359
Working with Intervals 361
Modeling and Querying Continuous Intervals 362
Modeling and Querying Independent Intervals 369
verlapping Intervals 373
Time Slicing 377
Modeling Durations 380
Managing Bitemporal Data 381
Summary 385
CHAPTER 12 Trees, Hierarchies, and Graphs 386
Terminology: Everything Is a Graph 386
The Basics: Adjacency Lists and Graphs 388
Constraining the Edges 389
Basic Graph Queries: Who Am I Connected To? 391
Traversing the Graph 393
Adjacency List Hierarchies 403
Finding Direct Descendants 404
Traversing down the Hierarchy 406
Ordering the Output 407
Are CTEs the Best Choice? 411
Traversing up the Hierarchy 415
Inserting New Nodes and Relocating Subtrees 416
Deleting Existing Nodes 417
Constraining the Hierarchy 417
Persisted Materialized Paths 420
Finding Subordinates 421
Navigating up the Hierarchy 422
Inserting Nodes 423
Relocating Subtrees 425
Deleting Nodes 426
Constraining the Hierarchy 427
The hierarchyid Datatype 427
Finding Subordinates 428
Navigating up the Hierarchy 429
Inserting Nodes 430
Relocating Subtrees 431
Deleting Nodes 432
Constraining the Hierarchy 432
Summary 433
Index 434