book

Expert SQL Server 2008 Development

Name: Expert SQL Server 2008 Development
ISBN: 9781430272137

by Alastair Aitchison, Adam Machanic

January 2010

Intermediate to advanced

430 pages

12h 19m

English

Apress

Read now

Unlock full access

Copyright
About the Author
About the Technical Reviewer
Acknowledgments
Preface
1. Software Development Methodologies for the Database World
1.1. Architecture Revisited1.1.1. Coupling1.1.2. Cohesion1.1.3. Encapsulation1.1.4. Interfaces1.1.5. Interfaces As Contracts1.1.6. Interface Design1.2. Integrating Databases and Object-Oriented Systems1.2.1. Data Logic1.2.2. Business Logic1.2.3. Application Logic1.3. The "Object-Relational Impedance Mismatch"1.3.1. Are Tables Really Classes in Disguise?1.3.2. Modeling Inheritance1.4. ORM: A Solution That Creates Many Problems1.5. Introducing the Database-As-API Mindset1.6. The Great Balancing Act1.6.1. Performance1.6.2. Testability1.6.3. Maintainability1.6.4. Security1.6.5. Allowing for Future Requirements1.7. Summary
2. Best Practices for Database Programming
2.1. Defensive Programming2.1.1. Attitudes to Defensive Programming2.1.2. Why Use a Defensive Approach to Database Development?2.2. Best Practice SQL Programming Techniques2.2.1. Identify Hidden Assumptions in Your Code2.2.2. Don't Take Shortcuts2.2.3. Testing2.2.4. Code Review2.2.5. Validate All Input2.2.6. Future-proof Your Code2.2.7. Limit Your Exposure2.2.8. Exercise Good Coding Etiquette2.2.8.1. Comments2.2.8.2. Indentations and Statement Blocks2.2.9. If All Else Fails. . .2.3. Creating a Healthy Development Environment2.4. Summary
3. Testing Database Routines
3.1. Approaches to Testing3.2. Unit and Functional Testing3.2.1. Unit Testing Frameworks3.2.2. Regression Testing3.3. Guidelines for Implementing Database Testing Processes and Procedures3.3.1. Why Is Testing Important?3.3.2. What Kind of Testing Is Important?3.3.3. How Many Tests Are Needed?3.3.4. Will Management Buy In?3.4. Performance Monitoring Tools3.4.1. Real-Time Client-Side Monitoring3.4.2. Server-Side Traces3.4.3. System Monitoring3.4.4. Dynamic Management Views (DMVs)3.4.5. Extended Events3.4.6. Data Collector3.5. Analyzing Performance Data3.5.1. Capturing Baseline Metrics3.5.2. Big-Picture Analysis3.5.3. Granular Analysis3.5.4. Fixing Problems: Is It Sufficient to Focus on the Obvious?3.6. Summary
4. Errors and Exceptions
4.1. Exceptions vs. Errors4.2. How Exceptions Work in SQL Server4.2.1. Statement-Level Exceptions4.2.2. Batch-Level Exceptions4.2.3. Parsing and Scope-Resolution Exceptions4.2.4. Connection and Server-Level Exceptions4.2.5. The XACT_ABORT Setting4.2.6. Dissecting an Error Message4.2.6.1. Error Number4.2.6.2. Error Level4.2.6.3. Error State4.2.6.4. Additional Information4.2.7. SQL Server's RAISERROR Function4.2.7.1. Formatting Error Messages4.2.7.2. Creating Persistent Custom Error Messages4.2.7.3. Logging User-Thrown Exceptions4.2.8. Monitoring Exception Events with Traces4.3. Exception Handling4.3.1. Why Handle Exceptions in T-SQL?4.3.2. Exception "Handling" Using @@ERROR4.3.3. SQL Server's TRY/CATCH Syntax4.3.3.1. Getting Extended Error Information in the Catch Block4.3.3.2. Rethrowing Exceptions4.3.3.3. When Should TRY/CATCH Be Used?4.3.3.4. Using TRY/CATCH to Build Retry Logic4.3.4. Exception Handling and SQLCLR4.4. Transactions and Exceptions4.4.1. The Myths of Transaction Abortion4.4.2. XACT_ABORT: Turning Myth into (Semi-)Reality4.4.3. TRY/CATCH and Doomed Transactions4.5. Summary
5. Privilege and Authorization
5.1. The Principle of Least Privilege5.1.1. Creating Proxies in SQL Server5.1.1.1. Server-Level Proxies5.1.1.2. Database-Level Proxies5.1.2. Data Security in Layers: The Onion Model5.2. Data Organization Using Schemas5.3. Basic Impersonation Using EXECUTE AS5.4. Ownership Chaining5.5. Privilege Escalation Without Ownership Chains5.5.1. Stored Procedures and EXECUTE AS5.5.2. Stored Procedure Signing Using Certificates5.5.3. Assigning Server-Level Permissions5.6. Summary

6. Encryption
6.1. Do You Really Need Encryption?6.1.1. What Should Be Protected?6.1.2. What Are You Protecting Against?6.2. SQL Server 2008 Encryption Key Hierarchy6.2.1. The Automatic Key Management Hierarchy6.2.1.1. Symmetric Keys, Asymmetric Keys, and Certificates6.2.1.2. Database Master Key6.2.1.3. Service Master Key6.2.2. Alternative Encryption Management Structures6.2.2.1. Symmetric Key Layering and Rotation6.2.2.2. Removing Keys from the Automatic Encryption Hierarchy6.2.2.3. Extensible Key Management6.3. Data Protection and Encryption Methods6.3.1. Hashing6.3.2. Symmetric Key Encryption6.3.3. Asymmetric Key Encryption6.3.4. Transparent Data Encryption6.4. Balancing Performance and Security6.5. Implications of Encryption on Query Design6.5.1. Equality Matching Using Hashed Message Authentication Codes6.5.2. Wildcard Searches Using HMAC Substrings6.5.3. Range Searches6.6. Summary
7. SQLCLR: Architecture and Design Considerations
7.1. Bridging the SQL/CLR Gap: The SqlTypes Library7.2. Wrapping Code to Promote Cross-Tier Reuse7.2.1. The Problem7.2.2. One Reasonable Solution7.2.3. A Simple Example: E-Mail Address Format Validation7.3. SQLCLR Security and Reliability Features7.3.1. Security Exceptions7.3.2. Host Protection Exceptions7.3.3. The Quest for Code Safety7.3.4. Selective Privilege Escalation via Assembly References7.3.4.1. Working with Host Protection Privileges7.3.4.2. Working with Code Access Security Privileges7.3.5. Granting Cross-Assembly Privileges7.3.5.1. Database Trustworthiness7.3.5.2. Strong Naming7.4. Performance Comparison: SQLCLR vs. TSQL7.4.1. Creating a "Simple Sieve" for Prime Numbers7.4.2. Calculating Running Aggregates7.4.3. String Manipulation7.5. Enhancing Service Broker Scale-Out with SQLCLR7.5.1. XML Serialization7.5.2. XML Deserialization7.5.3. Binary Serialization with SQLCLR7.5.4. Binary Deserialization7.6. Summary
8. Dynamic T-SQL
8.1. Dynamic T-SQL vs. Ad Hoc T-SQL8.2. The Stored Procedure vs. Ad Hoc SQL Debate8.3. Why Go Dynamic?8.3.1. Compilation and Parameterization8.3.2. Auto-Parameterization8.3.3. Application-Level Parameterization8.3.4. Performance Implications of Parameterization and Caching8.4. Supporting Optional Parameters8.4.1. Optional Parameters via Static T-SQL8.4.2. Going Dynamic: Using EXECUTE8.4.3. SQL Injection8.4.4. sp_executesql: A Better EXECUTE8.4.5. Performance Comparison8.5. Dynamic SQL Security Considerations8.5.1. Permissions to Referenced Objects8.5.2. Interface Rules8.6. Summary
9. Designing Systems for Application Concurrency
9.1. The Business Side: What Should Happen When Processes Collide?9.2. Isolation Levels and Transactional Behavior9.2.1. Blocking Isolation Levels9.2.1.1. READ COMMITTED Isolation9.2.1.2. REPEATABLE READ Isolation9.2.1.3. SERIALIZABLE Isolation9.2.2. Nonblocking Isolation Levels9.2.2.1. READ UNCOMMITTED Isolation9.2.2.2. SNAPSHOT Isolation9.2.3. From Isolation to Concurrency Control9.3. Preparing for the Worst: Pessimistic Concurrency9.3.1. Progressing to a Solution9.3.2. Enforcing Pessimistic Locks at Write Time9.3.3. Application Locks: Generalizing Pessimistic Concurrency9.4. Hoping for the Best: Optimistic Concurrency9.5. Embracing Conflict: Multivalue Concurrency Control9.6. Sharing Resources Between Concurrent Users9.6.1. Controlling Resource Allocation9.6.2. Calculating Effective and Shared Maximum Resource Allocation9.6.3. Controlling Concurrent Request Processing9.7. Summary
10. Working with Spatial Data
10.1. Modeling Spatial Data10.1.1. Spatial Reference Systems10.1.1.1. Geographic Coordinate Systems10.1.1.2. Projected Coordinate Systems10.1.2. Applying Coordinate Systems to the Earth10.1.2.1. Datum10.1.2.2. Prime Meridian10.1.2.3. Projection10.1.3. Spatial Reference Identifiers10.2. Geography vs. Geometry10.2.1. Standards Compliance10.2.2. Accuracy10.2.3. Technical Limitations and Performance10.3. Creating Spatial Data10.3.1. Well-Known Text10.3.2. Well-Known Binary10.3.3. Geography Markup Language10.3.4. Importing Data10.4. Querying Spatial Data10.4.1. Nearest-Neighbor Queries10.4.2. Finding Locations Within a Given Bounding Box10.5. Spatial Indexing10.5.1. How Does a Spatial Index Work?10.5.2. Optimizing the Grid10.6. Summary
11. Working with Temporal Data
11.1. Modeling Time-Based Information11.2. SQL Server's Date/Time Data Types11.2.1. Input Date Formats11.2.2. Output Date Formatting11.2.3. Efficiently Querying Date/Time Columns11.2.4. Date/Time Calculations11.2.4.1. Truncating the Time Portion of a datetime Value11.2.4.2. Finding Relative Dates11.2.4.3. How Many Candles on the Birthday Cake?11.3. Defining Periods Using Calendar Tables11.4. Dealing with Time Zones11.4.1. Storing UTC Time11.4.2. Using the datetimeoffset Type11.5. Working with Intervals11.5.1. Modeling and Querying Continuous Intervals11.5.2. Modeling and Querying Independent Intervals11.5.3. Overlapping Intervals11.5.4. Time Slicing11.6. Modeling Durations11.7. Managing Bitemporal Data11.8. Summary
12. Trees, Hierarchies, and Graphs
12.1. Terminology: Everything Is a Graph12.2. The Basics: Adjacency Lists and Graphs12.2.1. Constraining the Edges12.2.2. Basic Graph Queries: Who Am I Connected To?12.2.3. Traversing the Graph12.3. Adjacency List Hierarchies12.3.1. Finding Direct Descendants12.3.2. Traversing down the Hierarchy12.3.2.1. Ordering the Output12.3.2.2. Are CTEs the Best Choice?12.3.3. Traversing up the Hierarchy12.3.4. Inserting New Nodes and Relocating Subtrees12.3.5. Deleting Existing Nodes12.3.6. Constraining the Hierarchy12.4. Persisted Materialized Paths12.4.1. Finding Subordinates12.4.2. Navigating up the Hierarchy12.4.3. Inserting Nodes12.4.4. Relocating Subtrees12.4.5. Deleting Nodes12.4.6. Constraining the Hierarchy12.5. The hierarchyid Datatype12.5.1. Finding Subordinates12.5.2. Navigating up the Hierarchy12.5.3. Inserting Nodes12.5.4. Relocating Subtrees12.5.5. Deleting Nodes12.5.6. Constraining the Hierarchy12.6. Summary

Content preview from Expert SQL Server 2008 Development

Chapter 12. Trees, Hierarchies, and Graphs

Although at times it may seem chaotic, the world around us is filled with structure and order. The universe itself is hierarchical in nature, made up of galaxies, stars, and planets. One of the natural hierarchies here on earth is the food chain that exists in the wild; a lion can certainly eat a zebra, but alas, a zebra will probably never dine on lion flesh. And of course, we're all familiar with corporate management hierarchies—which some companies try to kill off in favor of matrixes, which are not hierarchical at all . . . but more on that later!

We strive to describe our existence based on connections between entities—or lack thereof—and that's what trees, hierarchies, and graphs help us do at the ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781430272137Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Expert SQL Server 2008 Development

by Alastair Aitchison, Adam Machanic

Chapter 12. Trees, Hierarchies, and Graphs

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.