Expert SQL server 2008 development Description based on print version record. - Includes index

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 expounds. Promotes expert-level practices; Leads to highly-performing and scalable code; Improves productivity, getting you home in time for dinner. 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

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

What you’ll learn

• How to productively handle errors and exceptions.
• How to exploit features for managing spatial and temporal data.
• How to manage tree-structured data using the new, hierarchid type.
• How to write code that easily survives high concurrency.
• How to program defensively.
• How to create proper and reusable test environments for your code.
• How to secure code and data through encryption and proper application of privilege.

Who this book is for

SQL Server developers and administrators.

Table of Contents

1. Software Development Methodologies for the Database World
2. Best Practices for Database Programming
3. Testing Database Routines
4. Errors and Exceptions
5. Privilege and Authorization
6. Encryption
7. SQLCLR: Architecture and Design Considerations
8. Dynamic T-SQL
9. Designing Systems for Application Concurrency
10. Working with Spatial Data
11. Working with Temporal Data
12. Trees, Hierarchies, and Graphs

__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