Expert SQL Server 2005 Development

Expert SQL Server 2005 Development

Released May 2007
Publisher(s): Apress
ISBN: 9781590597293

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

While building on the skills you already have, Expert SQL Server 2005 Development will help you become an even better developer by focusing on best practices and demonstrating how to design high-performance, maintainable database applications.

This book starts by reintroducing the database as a integral part of the software development ecosystem. You'll learn how to think about SQL Server development as you would any other software development. For example, there's no reason you can't architect and test database routines just as you would architect and test application code. And nothing should stop you from implementing the types of exception handling and security rules that are considered so important in other tiers, even if they are usually ignored in the database.

You'll learn how to apply development methodologies like these to produce high-quality encryption and SQLCLR solutions. Furthermore, you'll discover how to exploit a variety of tools that SQL Server offers in order to properly use dynamic SQL and to improve concurrency in your applications. Finally, you'll become well versed in implementing spatial and temporal database designs, as well as approaching graph and hierarchy problems.

Table of contents

  2. Foreword
  3. About the Authors
  4. About the Technical Reviewer
  5. Acknowledgments
  6. Introduction
  7. 1. Software Development Methodologies for the Database World
    1. Architecture Revisited
      1. Coupling, Cohesion, and Encapsulation
      2. Interfaces
    2. The Central Problem: Integrating Databases and Object-Oriented Systems
      1. Where Should the Logic Go?
      2. The Object-Relational Impedance Mismatch
    3. ORM: A Solution That Creates Many Problems
    4. Introducing the Database-as-API Mindset
    5. The Great Balancing Act
      1. Testability
      2. Maintainability
      3. Security
      4. Performance
      5. Creeping Featurism
    6. Summary
  8. 2. Testing Database Routines
    1. Introduction to Black Box and White Box Testing
      1. Unit and Functional Testing
      2. Unit Testing Frameworks
      3. The Importance of Regression Testing
    2. Guidelines for Implementing Database Testing Processes and Procedures
      1. Why Is Testing Important?
      2. What Kind of Testing Is Important?
      3. How Many Tests Are Needed?
      4. Will Management Buy In?
    3. Performance Testing and Profiling Database Systems
      1. Capturing Baseline Metrics
      2. Profiling Using Traces and SQL Server Profiler
      3. Evaluating Performance Counters
      4. Big-Picture Analysis
      5. Granular Analysis
      6. Fixing Problems: Is Focusing on the Obvious Issues Enough?
    4. Introducing the SQLQueryStress Performance Testing Tool
    5. Summary
  9. 3. Errors and Exceptions
    1. Exceptions vs. Errors
    2. How Exceptions Work in SQL Server
      1. Statement-Level Exceptions
      2. Batch-Level Exceptions
      3. Parsing and Scope-Resolution Exceptions
      4. Connection and Server-Level Exceptions
      5. The XACT_ABORT Setting
      6. Dissecting an Error Message
      7. SQL Server's RAISERROR Function
      8. Monitoring Exception Events with Traces
    3. Exception Handling
      1. Why Handle Exceptions in T-SQL?
      2. Exception "Handling" Using @@ERROR
      3. SQL Server's TRY/CATCH Syntax
    4. Transactions and Exceptions
      1. The Myths of Transaction Abortion
      2. XACT_ABORT: Turning Myth into (Semi-)Reality
      3. TRY/CATCH and Doomed Transactions
    5. Summary
  10. 4. Privilege and Authorization
    1. The Principle of Least Privilege
      1. Creating Proxies in SQL Server
      2. Data Security in Layers: The Onion Model
    2. Data Organization Using Schemas
    3. Basic Impersonation Using EXECUTE AS
    4. Ownership Chaining
    5. Privilege Escalation Without Ownership Chains
      1. Stored Procedures and EXECUTE AS
      2. Stored Procedure Signing Using Certificates
    6. Summary
  11. 5. Encryption
    1. What to Protect
    2. Encryption Terminology: What You Need to Know
    3. SQL Server 2005 Encryption Key Hierarchy
      1. Service Master Key
      2. Database Master Key
    4. SQL Server 2005 Data Protection
      1. HashBytes()
      2. Asymmetric Key and Certificate Encryption
      3. Symmetric Key Encryption
      4. EncryptByPassphrase
    5. Securing Data from the DBA
    6. Architecting for Performance
      1. Setting Up the Solution and Defining the Problem
      2. Searching Encrypted Data
    7. Summary
  12. 6. SQLCLR: Architecture and Design Considerations
    1. Bridging the SQL/CLR Gap: the SqlTypes Library
    2. Wrapping Code to Promote Cross-Tier Reuse
      1. A Simple Example: E-Mail Address Format Validation
    3. SQLCLR Security and Reliability Features
      1. The Quest for Code Safety
      2. Selective Privilege Escalation via Assembly References
      3. Granting Cross-Assembly Privileges
    4. Enhancing Service Broker Scale-Out with SQLCLR
    5. Extending User-Defined Aggregates
    6. Summary
  13. 7. Dynamic T-SQL
    1. Dynamic T-SQL vs. Ad Hoc T-SQL
      1. The Stored Procedure vs. Ad Hoc SQL Debate
    2. Why Go Dynamic?
      1. Compilation and Parameterization
      2. Auto-Parameterization
      3. Application-Level Parameterization
      4. Performance Implications of Parameterization and Caching
    3. Supporting Optional Parameters
      1. Optional Parameters via Static T-SQL
      2. Going Dynamic: Using EXECUTE
      3. SQL Injection
      4. sp_executesql: A Better EXECUTE
    4. Dynamic SQL Security Considerations
      1. Permissions to Referenced Objects
      2. Interface Rules
    5. Summary
  14. 8. Designing Systems for Application Concurrency
    1. The Business Side: What Should Happen When Processes Collide?
      1. A Brief Overview of SQL Server Isolation Levels
      2. Concurrency Control and SQL Server's Native Isolation Levels
    2. Preparing for the Worst: Pessimistic Concurrency
      1. Enforcing Pessimistic Locks at Write Time
      2. Application Locks: Generalizing Pessimistic Concurrency
    3. Hoping for the Best: Optimistic Concurrency
    4. Embracing Conflict: Multivalue Concurrency
    5. Extending Scalability Through Queuing
    6. Summary
  15. 9. Working with Spatial Data
    1. Representing Geospatial Data by Latitude and Longitude
      1. Setting Up Sample Data
      2. Calculating the Distance Between Two Points
      3. Moving from Point to Point
      4. Searching the Neighborhood
      5. The Bounding Box
      6. Finding the Nearest Neighbor
      7. The Dynamic Bounding Box
      8. Conclusion
    2. Representing Geospatial Data by Using the Hierarchical Triangular Mesh
      1. A Simplified Description of HTM
      2. Implementing the HtmID
      3. Functions in the Spatial Database
      4. Conclusion
    3. Other Types of Spatial Data
      1. Three-Dimensional Data
      2. Astronomical Data
      3. Virtual Space
      4. Representing Regions As Polygons
    4. Summary
  16. 10. Working with Temporal Data
    1. Representing More Than Just Time
    2. SQL Server's Date/Time Data Types
      1. Input Date Formats
      2. Output Date Formatting
      3. Efficiently Querying Date/Time Columns
      4. Date/Time Calculations
    3. Defining Periods Using Calendar Tables
    4. Designing and Querying Temporal Data Stores
      1. Dealing with Time Zones
      2. Working with Intervals
      3. Modeling Durations
      4. Managing Bitemporal Data
    5. Summary
  17. 11. Trees, Hierarchies, and Graphs
    1. Terminology: Everything Is a Graph
    2. The Basics: Adjacency Lists and Graphs
      1. Constraining the Edges
      2. Basic Graph Queries: Who Am I Connected To?
      3. Traversing the Graph
    3. Adjacency List Hierarchies
      1. Querying Adjacency List Hierarchies: The Basics
      2. Finding Direct Descendants
      3. Traversing down the Hierarchy
      4. Traversing up the Hierarchy
      5. Inserting New Nodes and Relocating Subtrees
      6. Deleting Existing Nodes
      7. Constraining the Hierarchy
    4. Persisting Materialized Paths
      1. Finding Subordinates
      2. Navigating up the Hierarchy
      3. Optimizing the Materialized Path Solution
      4. Inserting Nodes
      5. Relocating Subtrees
      6. Deleting Nodes
      7. Constraining the Hierarchy
    5. Nested Sets Model
      1. Finding Subordinates
      2. Navigating up the Hierarchy
      3. Inserting Nodes
      4. Relocating Subtrees
      5. Deleting Nodes
      6. Constraining the Hierarchy
    6. Summary

Product information

  • Title: Expert SQL Server 2005 Development
  • Author(s):
  • Release date: May 2007
  • Publisher(s): Apress
  • ISBN: 9781590597293