Expert SQL Server 2008 Development

Expert SQL Server 2008 Development

Released January 2010
Publisher(s): Apress
ISBN: 9781430272137

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

Table of contents

  1. Copyright
  2. About the Author
  3. About the Technical Reviewer
  4. Acknowledgments
  5. Preface
  6. 1. Software Development Methodologies for the Database World
    1. 1.1. Architecture Revisited
      1. 1.1.1. Coupling
      2. 1.1.2. Cohesion
      3. 1.1.3. Encapsulation
      4. 1.1.4. Interfaces
      5. 1.1.5. Interfaces As Contracts
      6. 1.1.6. Interface Design
    2. 1.2. Integrating Databases and Object-Oriented Systems
      1. 1.2.1. Data Logic
      2. 1.2.2. Business Logic
      3. 1.2.3. Application Logic
    3. 1.3. The "Object-Relational Impedance Mismatch"
      1. 1.3.1. Are Tables Really Classes in Disguise?
      2. 1.3.2. Modeling Inheritance
    4. 1.4. ORM: A Solution That Creates Many Problems
    5. 1.5. Introducing the Database-As-API Mindset
    6. 1.6. The Great Balancing Act
      1. 1.6.1. Performance
      2. 1.6.2. Testability
      3. 1.6.3. Maintainability
      4. 1.6.4. Security
      5. 1.6.5. Allowing for Future Requirements
    7. 1.7. Summary
  7. 2. Best Practices for Database Programming
    1. 2.1. Defensive Programming
      1. 2.1.1. Attitudes to Defensive Programming
      2. 2.1.2. Why Use a Defensive Approach to Database Development?
    2. 2.2. Best Practice SQL Programming Techniques
      1. 2.2.1. Identify Hidden Assumptions in Your Code
      2. 2.2.2. Don't Take Shortcuts
      3. 2.2.3. Testing
      4. 2.2.4. Code Review
      5. 2.2.5. Validate All Input
      6. 2.2.6. Future-proof Your Code
      7. 2.2.7. Limit Your Exposure
      8. 2.2.8. Exercise Good Coding Etiquette
        1. 2.2.8.1. Comments
        2. 2.2.8.2. Indentations and Statement Blocks
      9. 2.2.9. If All Else Fails. . .
    3. 2.3. Creating a Healthy Development Environment
    4. 2.4. Summary
  8. 3. Testing Database Routines
    1. 3.1. Approaches to Testing
    2. 3.2. Unit and Functional Testing
      1. 3.2.1. Unit Testing Frameworks
      2. 3.2.2. Regression Testing
    3. 3.3. Guidelines for Implementing Database Testing Processes and Procedures
      1. 3.3.1. Why Is Testing Important?
      2. 3.3.2. What Kind of Testing Is Important?
      3. 3.3.3. How Many Tests Are Needed?
      4. 3.3.4. Will Management Buy In?
    4. 3.4. Performance Monitoring Tools
      1. 3.4.1. Real-Time Client-Side Monitoring
      2. 3.4.2. Server-Side Traces
      3. 3.4.3. System Monitoring
      4. 3.4.4. Dynamic Management Views (DMVs)
      5. 3.4.5. Extended Events
      6. 3.4.6. Data Collector
    5. 3.5. Analyzing Performance Data
      1. 3.5.1. Capturing Baseline Metrics
      2. 3.5.2. Big-Picture Analysis
      3. 3.5.3. Granular Analysis
      4. 3.5.4. Fixing Problems: Is It Sufficient to Focus on the Obvious?
    6. 3.6. Summary
  9. 4. Errors and Exceptions
    1. 4.1. Exceptions vs. Errors
    2. 4.2. How Exceptions Work in SQL Server
      1. 4.2.1. Statement-Level Exceptions
      2. 4.2.2. Batch-Level Exceptions
      3. 4.2.3. Parsing and Scope-Resolution Exceptions
      4. 4.2.4. Connection and Server-Level Exceptions
      5. 4.2.5. The XACT_ABORT Setting
      6. 4.2.6. Dissecting an Error Message
        1. 4.2.6.1. Error Number
        2. 4.2.6.2. Error Level
        3. 4.2.6.3. Error State
        4. 4.2.6.4. Additional Information
      7. 4.2.7. SQL Server's RAISERROR Function
        1. 4.2.7.1. Formatting Error Messages
        2. 4.2.7.2. Creating Persistent Custom Error Messages
        3. 4.2.7.3. Logging User-Thrown Exceptions
      8. 4.2.8. Monitoring Exception Events with Traces
    3. 4.3. Exception Handling
      1. 4.3.1. Why Handle Exceptions in T-SQL?
      2. 4.3.2. Exception "Handling" Using @@ERROR
      3. 4.3.3. SQL Server's TRY/CATCH Syntax
        1. 4.3.3.1. Getting Extended Error Information in the Catch Block
        2. 4.3.3.2. Rethrowing Exceptions
        3. 4.3.3.3. When Should TRY/CATCH Be Used?
        4. 4.3.3.4. Using TRY/CATCH to Build Retry Logic
      4. 4.3.4. Exception Handling and SQLCLR
    4. 4.4. Transactions and Exceptions
      1. 4.4.1. The Myths of Transaction Abortion
      2. 4.4.2. XACT_ABORT: Turning Myth into (Semi-)Reality
      3. 4.4.3. TRY/CATCH and Doomed Transactions
    5. 4.5. Summary
  10. 5. Privilege and Authorization
    1. 5.1. The Principle of Least Privilege
      1. 5.1.1. Creating Proxies in SQL Server
        1. 5.1.1.1. Server-Level Proxies
        2. 5.1.1.2. Database-Level Proxies
      2. 5.1.2. Data Security in Layers: The Onion Model
    2. 5.2. Data Organization Using Schemas
    3. 5.3. Basic Impersonation Using EXECUTE AS
    4. 5.4. Ownership Chaining
    5. 5.5. Privilege Escalation Without Ownership Chains
      1. 5.5.1. Stored Procedures and EXECUTE AS
      2. 5.5.2. Stored Procedure Signing Using Certificates
      3. 5.5.3. Assigning Server-Level Permissions
    6. 5.6. Summary
  11. 6. Encryption
    1. 6.1. Do You Really Need Encryption?
      1. 6.1.1. What Should Be Protected?
      2. 6.1.2. What Are You Protecting Against?
    2. 6.2. SQL Server 2008 Encryption Key Hierarchy
      1. 6.2.1. The Automatic Key Management Hierarchy
        1. 6.2.1.1. Symmetric Keys, Asymmetric Keys, and Certificates
        2. 6.2.1.2. Database Master Key
        3. 6.2.1.3. Service Master Key
      2. 6.2.2. Alternative Encryption Management Structures
        1. 6.2.2.1. Symmetric Key Layering and Rotation
        2. 6.2.2.2. Removing Keys from the Automatic Encryption Hierarchy
        3. 6.2.2.3. Extensible Key Management
    3. 6.3. Data Protection and Encryption Methods
      1. 6.3.1. Hashing
      2. 6.3.2. Symmetric Key Encryption
      3. 6.3.3. Asymmetric Key Encryption
      4. 6.3.4. Transparent Data Encryption
    4. 6.4. Balancing Performance and Security
    5. 6.5. Implications of Encryption on Query Design
      1. 6.5.1. Equality Matching Using Hashed Message Authentication Codes
      2. 6.5.2. Wildcard Searches Using HMAC Substrings
      3. 6.5.3. Range Searches
    6. 6.6. Summary
  12. 7. SQLCLR: Architecture and Design Considerations
    1. 7.1. Bridging the SQL/CLR Gap: The SqlTypes Library
    2. 7.2. Wrapping Code to Promote Cross-Tier Reuse
      1. 7.2.1. The Problem
      2. 7.2.2. One Reasonable Solution
      3. 7.2.3. A Simple Example: E-Mail Address Format Validation
    3. 7.3. SQLCLR Security and Reliability Features
      1. 7.3.1. Security Exceptions
      2. 7.3.2. Host Protection Exceptions
      3. 7.3.3. The Quest for Code Safety
      4. 7.3.4. Selective Privilege Escalation via Assembly References
        1. 7.3.4.1. Working with Host Protection Privileges
        2. 7.3.4.2. Working with Code Access Security Privileges
      5. 7.3.5. Granting Cross-Assembly Privileges
        1. 7.3.5.1. Database Trustworthiness
        2. 7.3.5.2. Strong Naming
    4. 7.4. Performance Comparison: SQLCLR vs. TSQL
      1. 7.4.1. Creating a "Simple Sieve" for Prime Numbers
      2. 7.4.2. Calculating Running Aggregates
      3. 7.4.3. String Manipulation
    5. 7.5. Enhancing Service Broker Scale-Out with SQLCLR
      1. 7.5.1. XML Serialization
      2. 7.5.2. XML Deserialization
      3. 7.5.3. Binary Serialization with SQLCLR
      4. 7.5.4. Binary Deserialization
    6. 7.6. Summary
  13. 8. Dynamic T-SQL
    1. 8.1. Dynamic T-SQL vs. Ad Hoc T-SQL
    2. 8.2. The Stored Procedure vs. Ad Hoc SQL Debate
    3. 8.3. Why Go Dynamic?
      1. 8.3.1. Compilation and Parameterization
      2. 8.3.2. Auto-Parameterization
      3. 8.3.3. Application-Level Parameterization
      4. 8.3.4. Performance Implications of Parameterization and Caching
    4. 8.4. Supporting Optional Parameters
      1. 8.4.1. Optional Parameters via Static T-SQL
      2. 8.4.2. Going Dynamic: Using EXECUTE
      3. 8.4.3. SQL Injection
      4. 8.4.4. sp_executesql: A Better EXECUTE
      5. 8.4.5. Performance Comparison
    5. 8.5. Dynamic SQL Security Considerations
      1. 8.5.1. Permissions to Referenced Objects
      2. 8.5.2. Interface Rules
    6. 8.6. Summary
  14. 9. Designing Systems for Application Concurrency
    1. 9.1. The Business Side: What Should Happen When Processes Collide?
    2. 9.2. Isolation Levels and Transactional Behavior
      1. 9.2.1. Blocking Isolation Levels
        1. 9.2.1.1. READ COMMITTED Isolation
        2. 9.2.1.2. REPEATABLE READ Isolation
        3. 9.2.1.3. SERIALIZABLE Isolation
      2. 9.2.2. Nonblocking Isolation Levels
        1. 9.2.2.1. READ UNCOMMITTED Isolation
        2. 9.2.2.2. SNAPSHOT Isolation
      3. 9.2.3. From Isolation to Concurrency Control
    3. 9.3. Preparing for the Worst: Pessimistic Concurrency
      1. 9.3.1. Progressing to a Solution
      2. 9.3.2. Enforcing Pessimistic Locks at Write Time
      3. 9.3.3. Application Locks: Generalizing Pessimistic Concurrency
    4. 9.4. Hoping for the Best: Optimistic Concurrency
    5. 9.5. Embracing Conflict: Multivalue Concurrency Control
    6. 9.6. Sharing Resources Between Concurrent Users
      1. 9.6.1. Controlling Resource Allocation
      2. 9.6.2. Calculating Effective and Shared Maximum Resource Allocation
      3. 9.6.3. Controlling Concurrent Request Processing
    7. 9.7. Summary
  15. 10. Working with Spatial Data
    1. 10.1. Modeling Spatial Data
      1. 10.1.1. Spatial Reference Systems
        1. 10.1.1.1. Geographic Coordinate Systems
        2. 10.1.1.2. Projected Coordinate Systems
      2. 10.1.2. Applying Coordinate Systems to the Earth
        1. 10.1.2.1. Datum
        2. 10.1.2.2. Prime Meridian
        3. 10.1.2.3. Projection
      3. 10.1.3. Spatial Reference Identifiers
    2. 10.2. Geography vs. Geometry
      1. 10.2.1. Standards Compliance
      2. 10.2.2. Accuracy
      3. 10.2.3. Technical Limitations and Performance
    3. 10.3. Creating Spatial Data
      1. 10.3.1. Well-Known Text
      2. 10.3.2. Well-Known Binary
      3. 10.3.3. Geography Markup Language
      4. 10.3.4. Importing Data
    4. 10.4. Querying Spatial Data
      1. 10.4.1. Nearest-Neighbor Queries
      2. 10.4.2. Finding Locations Within a Given Bounding Box
    5. 10.5. Spatial Indexing
      1. 10.5.1. How Does a Spatial Index Work?
      2. 10.5.2. Optimizing the Grid
    6. 10.6. Summary
  16. 11. Working with Temporal Data
    1. 11.1. Modeling Time-Based Information
    2. 11.2. SQL Server's Date/Time Data Types
      1. 11.2.1. Input Date Formats
      2. 11.2.2. Output Date Formatting
      3. 11.2.3. Efficiently Querying Date/Time Columns
      4. 11.2.4. Date/Time Calculations
        1. 11.2.4.1. Truncating the Time Portion of a datetime Value
        2. 11.2.4.2. Finding Relative Dates
        3. 11.2.4.3. How Many Candles on the Birthday Cake?
    3. 11.3. Defining Periods Using Calendar Tables
    4. 11.4. Dealing with Time Zones
      1. 11.4.1. Storing UTC Time
      2. 11.4.2. Using the datetimeoffset Type
    5. 11.5. Working with Intervals
      1. 11.5.1. Modeling and Querying Continuous Intervals
      2. 11.5.2. Modeling and Querying Independent Intervals
      3. 11.5.3. Overlapping Intervals
      4. 11.5.4. Time Slicing
    6. 11.6. Modeling Durations
    7. 11.7. Managing Bitemporal Data
    8. 11.8. Summary
  17. 12. Trees, Hierarchies, and Graphs
    1. 12.1. Terminology: Everything Is a Graph
    2. 12.2. The Basics: Adjacency Lists and Graphs
      1. 12.2.1. Constraining the Edges
      2. 12.2.2. Basic Graph Queries: Who Am I Connected To?
      3. 12.2.3. Traversing the Graph
    3. 12.3. Adjacency List Hierarchies
      1. 12.3.1. Finding Direct Descendants
      2. 12.3.2. Traversing down the Hierarchy
        1. 12.3.2.1. Ordering the Output
        2. 12.3.2.2. Are CTEs the Best Choice?
      3. 12.3.3. Traversing up the Hierarchy
      4. 12.3.4. Inserting New Nodes and Relocating Subtrees
      5. 12.3.5. Deleting Existing Nodes
      6. 12.3.6. Constraining the Hierarchy
    4. 12.4. Persisted Materialized Paths
      1. 12.4.1. Finding Subordinates
      2. 12.4.2. Navigating up the Hierarchy
      3. 12.4.3. Inserting Nodes
      4. 12.4.4. Relocating Subtrees
      5. 12.4.5. Deleting Nodes
      6. 12.4.6. Constraining the Hierarchy
    5. 12.5. The hierarchyid Datatype
      1. 12.5.1. Finding Subordinates
      2. 12.5.2. Navigating up the Hierarchy
      3. 12.5.3. Inserting Nodes
      4. 12.5.4. Relocating Subtrees
      5. 12.5.5. Deleting Nodes
      6. 12.5.6. Constraining the Hierarchy
    6. 12.6. Summary

Product information

  • Title: Expert SQL Server 2008 Development
  • Author(s):
  • Release date: January 2010
  • Publisher(s): Apress
  • ISBN: 9781430272137