Aitchison / Machanic Expert SQL Server 2008 Development

1. ed
ISBN: 978-1-4302-7212-0
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 430 Seiten

Expert SQL Server 2008 Development is aimed at SQL Server developers ready to move beyond Books Online. Author and experienced developer Alastair Aitchison shows you how to think about SQL Server development as if it were any other type of development. You'll learn to manage testing in SQL Server and to properly deal with errors and exceptions. The book also covers critical, database-centric topics such as managing concurrency and securing your data and code through proper privileges and authorization. Alastair places focus on sound development and architectural practices that will help you become a better developer, capable of designing high-performance, robust, maintainable database applications. He shows you how to apply notable features in SQL Server such as encryption and support for hierarchical data. If developing for SQL Server is what puts the bread on your table, you can do no better than to read this book and to assimilate the expert-level practices that it provides. Promotes expert-level practices Leads to high performance, scalable code Improves productivity, getting you home in time for dinner

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.

Autoren/Hrsg.

Aitchison, Alastair

Machanic, Adam

Inhaltsverzeichnis

1;Contents at a Glance;5
2;Table of contents ;6
3;About the Author;17
4;About the Technical Reviewer;18
5;Acknowledgments;19
6;Preface;20
7;CHAPTER 1 Software Development Methodologies for the Database World ;21
7.1;Architecture Revisited;21
7.1.1;Coupling;23
7.1.2;Cohesion;24
7.1.3;Encapsulation;25
7.1.4;Interfaces;25
7.1.5;Interfaces As Contracts;26
7.1.6;Interface Design;26
7.2;Integrating Databases and Object-Oriented Systems;28
7.2.1;Data Logic;30
7.2.2;Business Logic;31
7.2.3;Application Logic;32
7.3;The “Object-Relational Impedance Mismatch”;32
7.3.1;Are Tables Really Classes in Disguise?;33
7.3.2;Modeling Inheritance;34
7.4;ORM: A Solution That Creates Many Problems;37
7.5;Introducing the Database-As-API Mindset;38
7.6;The Great Balancing Act;39
7.6.1;Performance;39
7.6.2;Testability;40
7.6.3;Maintainability;40
7.6.4;Security;41
7.6.5;Allowing for Future Requirements;41
7.7;Summary;42
8;CHAPTER 2 Best Practices for Database Programming;43
8.1;Defensive Programming;43
8.1.1;Attitudes to Defensive Programming;44
8.1.2;Why Use a Defensive Approach to Database Development?;47
8.2;Best Practice SQL Programming Techniques;48
8.2.1;Identify Hidden Assumptions in Your Code;49
8.2.2;Don’t Take Shortcuts;53
8.2.3;Testing;56
8.2.4;Code Review;59
8.2.5;Validate All Input;60
8.2.6;Future-proof Your Code;62
8.2.7;Limit Your Exposure;63
8.2.8;Exercise Good Coding Etiquette;63
8.2.8.1;Comments;64
8.2.8.2;Indentations and Statement Blocks;65
8.2.9;If All Else Fails. . .;66
8.3;Creating a Healthy Development Environment;67
8.4;Summary;67
9;CHAPTER 3 Testing Database Routines;69
9.1;Approaches to Testing;69
9.2;Unit and Functional Testing;70
9.2.1;Unit Testing Frameworks;72
9.2.2;Regression Testing;74
9.3;Guidelines for Implementing Database Testing Processes and Procedures;75
9.3.1;Why Is Testing Important?;76
9.3.2;What Kind of Testing Is Important?;76
9.3.3;How Many Tests Are Needed?;77
9.3.4;Will Management Buy In?;77
9.4;Performance Monitoring Tools;78
9.4.1;Real-Time Client-Side Monitoring;79
9.4.2;Server-Side Traces;80
9.4.3;System Monitoring;81
9.4.4;Dynamic Management Views (DMVs);82
9.4.5;Extended Events;83
9.4.6;Data Collector;85
9.5;Analyzing Performance Data;87
9.5.1;Capturing Baseline Metrics;87
9.5.2;Big-Picture Analysis;88
9.5.3;Untitled;88
9.5.4;Fixing Problems: Is It Sufficient to Focus on the Obvious?;90
9.6;Summary;90
10;CHAPTER 4 Errors and Exceptions;91
10.1;Exceptions vs. Errors;91
10.2;How Exceptions Work in SQL Server;92
10.2.1;Statement-Level Exceptions;93
10.2.2;Batch-Level Exceptions;93
10.2.3;Parsing and Scope-Resolution Exceptions;95
10.2.4;Connection and Server-Level Exceptions;96
10.2.5;The XACT_ABORT Setting;97
10.2.6;Dissecting an Error Message;98
10.2.6.1;Error Number;98
10.2.6.2;Error Level;99
10.2.6.3;Error State;99
10.2.6.4;Additional Information;100
10.2.7;SQL Server’s RAISERROR Function;101
10.2.7.1;Formatting Error Messages;102
10.2.7.2;Creating Persistent Custom Error Messages;103
10.2.7.3;Logging User-Thrown Exceptions;105
10.2.8;Monitoring Exception Events with Traces;105
10.3;Exception Handling;105
10.3.1;Why Handle Exceptions in T-SQL?;106
10.3.2;Exception “Handling” Using @@ERROR;106
10.3.3;SQL Server’s TRY/CATCH Syntax;107
10.3.3.1;Getting Extended Error Information in the Catch Block;109
10.3.3.2;Rethrowing Exceptions;110
10.3.3.3;When Should TRY/CATCH Be Used?;111
10.3.3.4;Using TRY/CATCH to Build Retry Logic;111
10.3.4;Exception Handling and SQLCLR;113
10.4;Transactions and Exceptions;116
10.4.1;The Myths of Transaction Abortion;116
10.4.2;XACT_ABORT: Turning Myth into (Semi-)Reality;118
10.4.3;TRY/CATCH and Doomed Transactions;119
10.5;Summary;120
11;CHAPTER 5 Privilege and Authorization;121
11.1;The Principle of Least Privilege;122
11.1.1;Creating Proxies in SQL Server;123
11.1.1.1;Server-Level Proxies;123
11.1.1.2;Database-Level Proxies;124
11.1.2;Data Security in Layers: The Onion Model;124
11.2;Data Organization Using Schemas;125
11.3;Basic Impersonation Using EXECUTE AS;127
11.4;Ownership Chaining;130
11.5;Privilege Escalation Without Ownership Chains;132
11.5.1;Stored Procedures and EXECUTE AS;132
11.5.2;Stored Procedure Signing Using Certificates;134
11.5.3;Assigning Server-Level Permissions;137
11.6;Summary;139
12;CHAPTER 6 Encryption;140
12.1;Do You Really Need Encryption?;140
12.1.1;What Should Be Protected?;140
12.1.2;What Are You Protecting Against?;141
12.2;SQL Server 2008 Encryption Key Hierarchy;142
12.2.1;The Automatic Key Management Hierarchy;142
12.2.1.1;Symmetric Keys, Asymmetric Keys, and Certificates;143
12.2.1.2;Database Master Key;144
12.2.1.3;Service Master Key;144
12.2.2;Alternative Encryption Management Structures;144
12.2.2.1;Symmetric Key Layering and Rotation;145
12.2.2.2;Removing Keys from the Automatic Encryption Hierarchy;145
12.2.2.3;Extensible Key Management;146
12.3;Data Protection and Encryption Methods;147
12.3.1;Hashing;148
12.3.2;Symmetric Key Encryption;149
12.3.3;Asymmetric Key Encryption;153
12.3.4;Transparent Data Encryption;155
12.4;Balancing Performance and Security;158
12.5;Implications of Encryption on Query Design;164
12.5.1;Equality Matching Using Hashed Message Authentication Codes;167
12.5.2;Wildcard Searches Using HMAC Substrings;172
12.5.3;Range Searches;176
12.6;Summary;177
13;CHAPTER 7 SQLCLR: Architecture and Design Considerations;178
13.1;Bridging the SQL/CLR Gap: The SqlTypes Library;179
13.2;Wrapping Code to Promote Cross-Tier Reuse;180
13.2.1;The Problem;180
13.2.2;One Reasonable Solution;180
13.2.3;A Simple Example: E-Mail Address Format Validation;181
13.3;SQLCLR Security and Reliability Features;182
13.3.1;Security Exceptions;183
13.3.2;Host Protection Exceptions;184
13.3.3;The Quest for Code Safety;187
13.3.4;Selective Privilege Escalation via Assembly References;187
13.3.4.1;Working with Host Protection Privileges;188
13.3.4.2;Working with Code Access Security Privileges;192
13.3.5;Granting Cross-Assembly Privileges;194
13.3.5.1;Database Trustworthiness;194
13.3.5.2;Strong Naming;196
13.4;Performance Comparison: SQLCLR vs. TSQL;197
13.4.1;Creating a “Simple Sieve” for Prime Numbers;198
13.4.2;Calculating Running Aggregates;200
13.4.3;String Manipulation;202
13.5;Enhancing Service Broker Scale-Out with SQLCLR;204
13.5.1;XML Serialization;204
13.5.2;XML Deserialization;205
13.5.3;Binary Serialization with SQLCLR;206
13.5.4;Binary Deserialization;210
13.6;194Summary;213
14;CHAPTER 8 Dynamic T-SQL;214
14.1;Dynamic T-SQL vs. Ad Hoc T-SQL;215
14.2;The Stored Procedure vs. Ad Hoc SQL Debate;215
14.3;Why Go Dynamic?;216
14.3.1;Compilation and Parameterization;217
14.3.2;Auto-Parameterization;219
14.3.3;Application-Level Parameterization;221
14.3.4;Performance Implications of Parameterization and Caching;222
14.4;Supporting Optional Parameters;224
14.4.1;Optional Parameters via Static T-SQL;225
14.4.2;Going Dynamic: Using EXECUTE;231
14.4.3;SQL Injection;237
14.4.4;sp_executesql: A Better EXECUTE;239
14.4.5;Performance Comparison;242
14.5;Dynamic SQL Security Considerations;249
14.5.1;Permissions to Referenced Objects;249
14.5.2;Interface Rules;249
14.6;Summary;251
15;CHAPTER 9 Designing Systems for Application Concurrency;252
15.1;The Business Side: What Should Happen When Processes Collide?;253
15.2;Isolation Levels and Transactional Behavior;254
15.2.1;Blocking Isolation Levels;256
15.2.1.1;READ COMMITTED Isolation;256
15.2.1.2;REPEATABLE READ Isolation;256
15.2.1.3;SERIALIZABLE Isolation;257
15.2.2;Nonblocking Isolation Levels;258
15.2.2.1;READ UNCOMMITTED Isolation;258
15.2.2.2;SNAPSHOT Isolation;259
15.2.3;From Isolation to Concurrency Control;259
15.3;Preparing for the Worst: Pessimistic Concurrency;260
15.3.1;Progressing to a Solution;261
15.3.2;Enforcing Pessimistic Locks at Write Time;266
15.3.3;Application Locks: Generalizing Pessimistic Concurrency;267
15.4;Hoping for the Best: Optimistic Concurrency;277
15.5;Embracing Conflict: Multivalue Concurrency Control;283
15.6;Sharing Resources Between Concurrent Users;286
15.6.1;Controlling Resource Allocation;289
15.6.2;Calculating Effective and Shared Maximum Resource Allocation;294
15.6.3;Controlling Concurrent Request Processing;296
15.7;Summary;298
16;CHAPTER 10 Working with Spatial Data;299
16.1;Modeling Spatial Data;299
16.1.1;Spatial Reference Systems;302
16.1.1.1;Geographic Coordinate Systems;302
16.1.1.2;Projected Coordinate Systems;302
16.1.2;Applying Coordinate Systems to the Earth;304
16.1.2.1;Datum;304
16.1.2.2;Prime Meridian;304
16.1.2.3;Projection;305
16.1.3;Spatial Reference Identifiers;306
16.2;Geography vs. Geometry;308
16.2.1;Standards Compliance;309
16.2.2;Accuracy;310
16.2.3;Technical Limitations and Performance;310
16.3;Creating Spatial Data;312
16.3.1;Well-Known Text;312
16.3.2;Well-Known Binary;313
16.3.3;Geography Markup Language;314
16.3.4;Importing Data;314
16.4;Querying Spatial Data;318
16.4.1;Nearest-Neighbor Queries;320
16.4.2;Finding Locations Within a Given Bounding Box;324
16.5;Spatial Indexing;329
16.5.1;How Does a Spatial Index Work?;329
16.5.2;Optimizing the Grid;331
16.6;Summary;335
17;CHAPTER 11 Working with Temporal Data;336
17.1;Modeling Time-Based Information;336
17.2;SQL Server’s Date/Time Data Types;337
17.2.1;Input Date Formats;338
17.2.2;Output Date Formatting;340
17.2.3;Efficiently Querying Date/Time Columns;341
17.2.4;Date/Time Calculations;344
17.2.4.1;Truncating the Time Portion of a datetime Value;345
17.2.4.2;Finding Relative Dates;347
17.2.4.3;How Many Candles on the Birthday Cake?;350
17.3;Defining Periods Using Calendar Tables;351
17.4;Dealing with Time Zones;356
17.4.1;Storing UTC Time;358
17.4.2;Using the datetimeoffset Type;359
17.5;Working with Intervals;361
17.5.1;Modeling and Querying Continuous Intervals;362
17.5.2;Modeling and Querying Independent Intervals;369
17.5.3;verlapping Intervals;373
17.5.4;Time Slicing;377
17.6;Modeling Durations;380
17.7;Managing Bitemporal Data;381
17.8;Summary;385
18;CHAPTER 12 Trees, Hierarchies, and Graphs;386
18.1;Terminology: Everything Is a Graph;386
18.2;The Basics: Adjacency Lists and Graphs;388
18.2.1;Constraining the Edges;389
18.2.2;Basic Graph Queries: Who Am I Connected To?;391
18.2.3;Traversing the Graph;393
18.3;Adjacency List Hierarchies;403
18.3.1;Finding Direct Descendants;404
18.3.2;Traversing down the Hierarchy;406
18.3.2.1;Ordering the Output;407
18.3.2.2;Are CTEs the Best Choice?;411
18.3.3;Traversing up the Hierarchy;415
18.3.4;Inserting New Nodes and Relocating Subtrees;416
18.3.5;Deleting Existing Nodes;417
18.3.6;Constraining the Hierarchy;417
18.4;Persisted Materialized Paths;420
18.4.1;Finding Subordinates;421
18.4.2;Navigating up the Hierarchy;422
18.4.3;Inserting Nodes;423
18.4.4;Relocating Subtrees;425
18.4.5;Deleting Nodes;426
18.4.6;Constraining the Hierarchy;427
18.5;The hierarchyid Datatype;427
18.5.1;Finding Subordinates;428
18.5.2;Navigating up the Hierarchy;429
18.5.3;Inserting Nodes;430
18.5.4;Relocating Subtrees;431
18.5.5;Deleting Nodes;432
18.5.6;Constraining the Hierarchy;432
18.6;Summary;433
19;Index;434