You are previewing SQL Server™ 2005 Bible.
O'Reilly logo
SQL Server™ 2005 Bible

Book Description

Use this comprehensive tutorial and reference to increase productivity and write stored procedures using the language with which you're most familiar. The revised content covers new features such as XML integration, Web services, the .NET Common Language Runtime (CLR), and security updates, making this book a must for any developer or database administrator transitioning to the new version of SQL Server. You'll learn to develop SQL Server database and data connections, administer SQL Server, and keep databases performing at their peak. In addition, you'll find dozens of specific examples in both a graphical format and as SQL code as well as numerous best practices describing the most effective way to accomplish a given task. A companion Web site provides all of the code examples found in the book.

Table of Contents

  1. Copyright
  2. Credits
  3. About the Author
    1. About the Co-Authors and Contributors
  4. Foreword
  5. Acknowledgments
  6. Introduction
    1. Who Should Read This Book
    2. How This Book Is Organized
      1. Part I: Laying the Foundation
      2. Part II: Manipulating Data with Select
      3. Part III: Developing with SQL Server
      4. Part IV: Enterprise Data Management
      5. Part V: Business Intelligence
      6. Part VI: Optimization Strategies
      7. Part VII: Appendixes
    3. How to Use This Book
      1. Conventions and Features
        1. Icons
    4. What's on the Companion Website
    5. Where to Go from Here
  7. I. Laying the Foundation
    1. 1. The Information Architecture Principle
      1. 1.1. Simplicity vs. Complexity
        1. 1.1.1. Complexity
        2. 1.1.2. Simplicity
      2. 1.2. The Usability Objective
        1. 1.2.1. Suitability of Design
        2. 1.2.2. Data Store Configurations
        3. 1.2.3. Master Data Stores Design Styles
          1. 1.2.3.1. Relational DBMSs
          2. 1.2.3.2. Object-Oriented DBMSs
          3. 1.2.3.3. Generic Pattern DBMS
      3. 1.3. Data Integrity
        1. 1.3.1. Entity Integrity
        2. 1.3.2. Domain Integrity
        3. 1.3.3. Referential Integrity
        4. 1.3.4. User-Defined Integrity
        5. 1.3.5. Transactional Integrity
        6. 1.3.6. Transactional Faults
          1. 1.3.6.1. Dirty Reads
          2. 1.3.6.2. Non-Repeatable Reads
          3. 1.3.6.3. Phantom Rows
          4. 1.3.6.4. Lost Updates
          5. 1.3.6.5. Deadlocks
        7. 1.3.7. Isolation Levels
        8. 1.3.8. The Value of Null
          1. 1.3.8.1. Working with Nulls
          2. 1.3.8.2. Null Controversy
      4. 1.4. Performance
        1. 1.4.1. Design
        2. 1.4.2. Set-Based Processing
        3. 1.4.3. Indexing
        4. 1.4.4. Partitioning
        5. 1.4.5. Caching
      5. 1.5. Availability
        1. 1.5.1. Redundancy
        2. 1.5.2. Recovery
      6. 1.6. Extensibility
        1. 1.6.1. Abstraction Layer
        2. 1.6.2. Generalization
      7. 1.7. Security
        1. 1.7.1. Restricted Access
        2. 1.7.2. Information Ownership
        3. 1.7.3. Audit Trails
      8. 1.8. Optimization Theory and SQL Server
        1. 1.8.1. Schema Design
        2. 1.8.2. Queries
        3. 1.8.3. Indexing
        4. 1.8.4. Concurrency
        5. 1.8.5. Advanced Scalability
      9. 1.9. Summary
    2. 2. Relational Database Modeling
      1. 2.1. Modeling Reality
      2. 2.2. Visible Entities
        1. 2.2.1. Every Tuple (Row) Is an Island
        2. 2.2.2. Primary Keys
        3. 2.2.3. Tables, Rows, Columns
      3. 2.3. Identifying Multiple Entities
        1. 2.3.1. Multiple Objects
        2. 2.3.2. Relationships Between Objects
        3. 2.3.3. Organizing or Grouping Objects
        4. 2.3.4. Consistent Lookup Values
        5. 2.3.5. Complex Objects
      4. 2.4. Relational Patterns
        1. 2.4.1. Secondary Entities and Foreign Keys
        2. 2.4.2. Relationship Cardinality
        3. 2.4.3. Relationship Optionality
        4. 2.4.4. Data-Model Diagramming
        5. 2.4.5. One-to-Many Relationships
        6. 2.4.6. One-to-One Relationships
        7. 2.4.7. Supertype/Subtype Relationships
        8. 2.4.8. Many-to-Many Relationships
        9. 2.4.9. Category Entities
        10. 2.4.10. Reflexive Relationships
      5. 2.5. Normalization
        1. 2.5.1. Entity/Attribute Design Principles
        2. 2.5.2. Normal Forms
        3. 2.5.3. Simplicity and Normalization
        4. 2.5.4. First Normal Form (1NF)
        5. 2.5.5. The Second Normal Form (2NF)
        6. 2.5.6. The Third Normal Form (3NF)
        7. 2.5.7. The Boyce-Codd Normal Form (BCNF)
        8. 2.5.8. The Fourth Normal Form (4NF)
        9. 2.5.9. The Fifth Normal Form (5NF)
      6. 2.6. Relational Algebra
      7. 2.7. Summary
    3. 3. Exploring SQL Server 2005 Architecture
      1. 3.1. Data Access Architectures
        1. 3.1.1. The Client/Server Database Model
          1. 3.1.1.1. Client/Server Databases
          2. 3.1.1.2. Client/Server Roles
        2. 3.1.2. N-Tier Design
        3. 3.1.3. Service-Oriented Architecture
      2. 3.2. SQL Server Services
        1. 3.2.1. Relational Engine
        2. 3.2.2. Transact-SQL
        3. 3.2.3. Visual Studio and the CLR
        4. 3.2.4. Service Broker
        5. 3.2.5. Replication Services
        6. 3.2.6. Full-Text Search
        7. 3.2.7. Notification Services
        8. 3.2.8. Server Management Objects
        9. 3.2.9. SQL Server Agent
        10. 3.2.10. Distributed Transaction Coordinator (DTC)
        11. 3.2.11. SQL Mail
      3. 3.3. Business Intelligence Services
        1. 3.3.1. Integration Services
        2. 3.3.2. Reporting Services
        3. 3.3.3. Analysis Services
      4. 3.4. SQL Server 2005 Editions
        1. 3.4.1. Enterprise (Developer) Edition
        2. 3.4.2. Standard Edition
        3. 3.4.3. Workgroup Edition
        4. 3.4.4. SQL Server Express Edition
        5. 3.4.5. SQL Server Everywhere Edition
      5. 3.5. SQL Server Tools and Components
        1. 3.5.1. SQL Server Management Studio
        2. 3.5.2. SQL Server Configuration Manager
        3. 3.5.3. Surface Area Configuration Tool
        4. 3.5.4. Business Intelligence Development Studio
        5. 3.5.5. SQL Integrated Help
        6. 3.5.6. SQL Profiler
        7. 3.5.7. Performance Monitor
        8. 3.5.8. Database Tuning Advisor
        9. 3.5.9. Command-Line Utilities: SQLCmd, Bulk Copy
      6. 3.6. SQL Server 2005 Feature Pack
      7. 3.7. AdventureWorks
      8. 3.8. Exploring the Metadata
        1. 3.8.1. System Databases
        2. 3.8.2. Metadata Views
      9. 3.9. Summary
    4. 4. Installing SQL Server 2005
      1. 4.1. Planning Your Installation
        1. 4.1.1. Operating System
        2. 4.1.2. Security Accounts
        3. 4.1.3. Authentication Mode
        4. 4.1.4. Server Instances
      2. 4.2. Hardware Recommendations
        1. 4.2.1. Dedicated Server
        2. 4.2.2. Copious Memory
        3. 4.2.3. Using Multiple CPUs
        4. 4.2.4. Disk-Drive Subsystems
        5. 4.2.5. RAID Disk Subsystems
        6. 4.2.6. Network Performance
      3. 4.3. Performing the Installation
        1. 4.3.1. Attended Installations
        2. 4.3.2. Unattended Installations
        3. 4.3.3. Remote Installations
        4. 4.3.4. Installing in a Clustered Environment
        5. 4.3.5. Installing Multiple Instances
        6. 4.3.6. Testing the Installation
      4. 4.4. Upgrading from Previous Versions
        1. 4.4.1. SQL Server 2005 Upgrade Advisor Tool
        2. 4.4.2. Upgrading from SQL Server 2000
      5. 4.5. Migrating to SQL Server
        1. 4.5.1. Migration Assistant
          1. 4.5.1.1. Assessment
        2. 4.5.2. Schema Conversion
        3. 4.5.3. Data Migration
        4. 4.5.4. Business Logic Conversion
        5. 4.5.5. Validation and Integration
      6. 4.6. Configuring the Surface Area of SQL Server
        1. 4.6.1. Surface Area Configuration Tool
          1. 4.6.1.1. Surface Area Configuration for Services and Connections
          2. 4.6.1.2. Surface Area Configuration for Features
            1. 4.6.1.2.1. Database Engine
            2. 4.6.1.2.2. Analysis Services
            3. 4.6.1.2.3. Reporting Services
        2. 4.6.2. Command Prompt Utilities
      7. 4.7. Removing SQL Server
      8. 4.8. Summary
    5. 5. Client Software Connectivity
      1. 5.1. Enabling Server Connectivity
        1. 5.1.1. Server Configuration Manager
        2. 5.1.2. SQL Native Client Connectivity (SNAC)
      2. 5.2. SQL Server Native Client Features
        1. 5.2.1. Requirements
        2. 5.2.2. Database Mirroring
        3. 5.2.3. Asynchronous Operations
        4. 5.2.4. Multiple Active Result Sets (MARS)
        5. 5.2.5. XML Data Types
        6. 5.2.6. User-Defined Types
        7. 5.2.7. Large Value Types
        8. 5.2.8. Handling Expired Passwords
        9. 5.2.9. Snapshot Isolation
      3. 5.3. Summary
    6. 6. Using Management Studio
      1. 6.1. Organizing the Interface
        1. 6.1.1. Window Placement
        2. 6.1.2. The Context Menu
        3. 6.1.3. The Summary Page
      2. 6.2. Registered Servers
      3. 6.3. Object Explorer
        1. 6.3.1. Navigating the Tree
        2. 6.3.2. Filtering Object Explorer
        3. 6.3.3. The Table Designer
        4. 6.3.4. Building Database Diagrams
        5. 6.3.5. The Query Designer
      4. 6.4. Using the Query Editor
        1. 6.4.1. Connecting to a Server
        2. 6.4.2. Opening a .sql File
        3. 6.4.3. Executing SQL Batches
        4. 6.4.4. Shortcuts and Bookmarks
        5. 6.4.5. Viewing Query Execution Plans
      5. 6.5. Using the Solution Explorer
      6. 6.6. Introducing the Templates
      7. 6.7. Summary
  8. II. Manipulating Data with Select
    1. 7. Understanding Basic Query Flow
      1. 7.1. Understanding Query Flow
        1. 7.1.1. Syntactical Flow of the Query Statement
        2. 7.1.2. A Graphical View of the Query Statement
        3. 7.1.3. Logical Flow of the Query Statement
        4. 7.1.4. Physical Flow of the Query Statement
      2. 7.2. From Clause Data Sources
        1. 7.2.1. Possible Data Sources
        2. 7.2.2. Named Ranges
        3. 7.2.3. [Table Name]
        4. 7.2.4. Four-Part Table Names
      3. 7.3. Where Conditions
        1. 7.3.1. Using the Between Search Condition
        2. 7.3.2. Using the In Search Condition
        3. 7.3.3. Using the Like Search Condition
        4. 7.3.4. Multiple Where Conditions
        5. 7.3.5. Select...Where
      4. 7.4. Ordering the Result Set
        1. 7.4.1. Specifying the Order by Using Column Names
        2. 7.4.2. Specifying the Order by Using Expressions
        3. 7.4.3. Specifying the Order by Using Column Aliases
        4. 7.4.4. Specifying the Order by Using Column Ordinal Position
        5. 7.4.5. Order by and Collation
      5. 7.5. Select Distinct
      6. 7.6. Ranking
        1. 7.6.1. Top
        2. 7.6.2. The With Ties Option
      7. 7.7. Summary
    2. 8. Using Expressions and Scalar Functions
      1. 8.1. Building Expressions
        1. 8.1.1. Operators
        2. 8.1.2. Bitwise Operators
          1. 8.1.2.1. Boolean And
          2. 8.1.2.2. Boolean Or
          3. 8.1.2.3. Boolean Exclusive Or
          4. 8.1.2.4. Bitwise Not
      2. 8.2. Case Expressions
        1. 8.2.1. Simple Case
        2. 8.2.2. Boolean Case
      3. 8.3. Working with Nulls
        1. 8.3.1. Testing for Null
        2. 8.3.2. Handling Nulls
          1. 8.3.2.1. Using the IsNull() Function
          2. 8.3.2.2. Coalesce()
          3. 8.3.2.3. Nullif()
          4. 8.3.2.4. Non-Default Null Behavior
      4. 8.4. Scalar Functions
        1. 8.4.1. User Information Functions
        2. 8.4.2. Data-Time Functions
        3. 8.4.3. String Functions
        4. 8.4.4. Soundex Functions
          1. 8.4.4.1. Using the Soundex() Function
          2. 8.4.4.2. Using the Difference() Soundex Function
        5. 8.4.5. Data-Type Conversion Functions
        6. 8.4.6. Server Environment Information
      5. 8.5. Summary
    3. 9. Merging Data with Joins and Unions
      1. 9.1. Using Joins
      2. 9.2. Inner Joins
        1. 9.2.1. Creating Inner Joins within SQL Code
        2. 9.2.2. Number of Rows Returned
        3. 9.2.3. ANSI SQL 89 Joins
        4. 9.2.4. Multiple Table Joins
      3. 9.3. Outer Joins
        1. 9.3.1. Outer Joins and Optional Foreign Keys
        2. 9.3.2. Full Outer Joins
        3. 9.3.3. Placing the Conditions within Outer Joins
      4. 9.4. Self-Joins
      5. 9.5. Cross (Unrestricted) Joins
      6. 9.6. Exotic Joins
        1. 9.6.1. Θ(theta) Joins
        2. 9.6.2. Multiple-Condition Joins
        3. 9.6.3. Non-Key Joins
      7. 9.7. Set Difference
      8. 9.8. Using Unions
        1. 9.8.1. Intersection Union
        2. 9.8.2. Difference Union/Except
      9. 9.9. Summary
    4. 10. Including Data with Subqueries and CTEs
      1. 10.1. Methods and Locations
      2. 10.2. Simple Subqueries
        1. 10.2.1. Common Table Expressions
        2. 10.2.2. Using Scalar Subqueries
        3. 10.2.3. Using Subqueries as Lists
        4. 10.2.4. Using Subqueries as Tables
      3. 10.3. Correlated Subqueries
      4. 10.4. Relational Division
        1. 10.4.1. Relational Division with a Remainder
        2. 10.4.2. Exact Relational Division
      5. 10.5. Summary
    5. 11. Aggregating Data
      1. 11.1. Simple Aggregations
        1. 11.1.1. Basic Aggregations
        2. 11.1.2. Beginning Statistics
      2. 11.2. Grouping within a Result Set
        1. 11.2.1. Simple Groupings
        2. 11.2.2. Aggravating Queries
          1. 11.2.2.1. Including group by Descriptions
          2. 11.2.2.2. Including All Group By Values
          3. 11.2.2.3. Nesting Aggregations
          4. 11.2.2.4. Including Detail Descriptions
          5. 11.2.2.5. Filtering Grouped Results
      3. 11.3. Generating Totals
        1. 11.3.1. Rollup Subtotals
        2. 11.3.2. Cube Queries
        3. 11.3.3. Computing Aggregates
      4. 11.4. Building Crosstab Queries
        1. 11.4.1. Fixed-Column Crosstab Queries
          1. 11.4.1.1. Correlated Subquery Method
          2. 11.4.1.2. Case Method
          3. 11.4.1.3. Pivot Method
        2. 11.4.2. Dynamic Crosstab Queries
          1. 11.4.2.1. Cursor and Pivot Method
          2. 11.4.2.2. Multiple Assignment Variable and Pivot Method
      5. 11.5. Summary
    6. 12. Navigating Hierarchical Data
      1. 12.1. Adjacency List Schema Patterns
        1. 12.1.1. Basic Adjacency List Pattern
        2. 12.1.2. Adjacency List Variations
          1. 12.1.2.1. Dual Parents
          2. 12.1.2.2. Multiple Cardinalities
      2. 12.2. Navigating the Adjacency List
        1. 12.2.1. Using a Standard select Statement
        2. 12.2.2. Using a Recursive Cursor
        3. 12.2.3. Using a Set-Based Solution
        4. 12.2.4. Using a User-Defined Function
        5. 12.2.5. Using Recursive Common Table Expressions
      3. 12.3. Summary
    7. 13. Using Full-Text Search
      1. 13.1. Configuring Full-Text Search Catalogs
        1. 13.1.1. Creating a Catalog with the Wizard
        2. 13.1.2. Creating a Catalog with T-SQL Code
        3. 13.1.3. Pushing Data to the Full-Text Index
        4. 13.1.4. Maintaining a Catalog with Management Studio
        5. 13.1.5. Maintaining a Catalog in T-SQL Code
        6. 13.1.6. Noise Files
      2. 13.2. Word Searches
        1. 13.2.1. The Contains Function
        2. 13.2.2. ContainsTable
      3. 13.3. Advanced Search Options
        1. 13.3.1. Multiple Word Searches
        2. 13.3.2. Searches with Wildcards
        3. 13.3.3. Phrase Searches
        4. 13.3.4. Word-Proximity Searches
        5. 13.3.5. Word-Inflection Searches
        6. 13.3.6. Thesaurus Searches
        7. 13.3.7. Variable-Word-Weight Searches
      4. 13.4. Fuzzy Searches
        1. 13.4.1. Freetext
        2. 13.4.2. FreetextTable
      5. 13.5. Binary Object Indexing
      6. 13.6. Performance
      7. 13.7. Summary
    8. 14. Creating Views
      1. 14.1. Why Use Views?
      2. 14.2. Working with Views
        1. 14.2.1. Creating Views Using Management Studio
        2. 14.2.2. Creating Views with DDL Code
        3. 14.2.3. Order By and Views
        4. 14.2.4. View Restrictions
        5. 14.2.5. Executing Views
      3. 14.3. Locking Down the View
        1. 14.3.1. Protecting the Data
        2. 14.3.2. Protecting the View
          1. 14.3.2.1. Schema Changes
          2. 14.3.2.2. Encrypting the View's select Statement
          3. 14.3.2.3. Application Metadata
      4. 14.4. Updating through Views
      5. 14.5. Nesting Views
      6. 14.6. Using Synonyms
      7. 14.7. Summary
    9. 15. Working with Distributed Queries
      1. 15.1. Distributed Query Concepts
      2. 15.2. Accessing a Local SQL Server Database
      3. 15.3. Linking to External Data Sources
        1. 15.3.1. Linking with Management Studio
          1. 15.3.1.1. Selecting the Server
          2. 15.3.1.2. Configuring the Logins
          3. 15.3.1.3. Configuring the Options
        2. 15.3.2. Linking with T-SQL
          1. 15.3.2.1. Establishing the Link
          2. 15.3.2.2. Distributed Security and Logins
          3. 15.3.2.3. Linked Server Options
        3. 15.3.3. Linking with Non-SQL Server Data Sources
          1. 15.3.3.1. Linking to Excel
          2. 15.3.3.2. Linking to MS Access
      4. 15.4. Developing Distributed Queries
        1. 15.4.1. Distributed Queries and Management Studio
        2. 15.4.2. Distributed Views
        3. 15.4.3. Local-Distributed Queries
          1. 15.4.3.1. Using the Four-Part Name
          2. 15.4.3.2. OpenDataSource()
        4. 15.4.4. Pass-Through Distributed Queries
          1. 15.4.4.1. Using the Four-Part Name
          2. 15.4.4.2. OpenQuery()
          3. 15.4.4.3. OpenRowSet()
      5. 15.5. Distributed Transactions
        1. 15.5.1. Distributed Transaction Coordinator
        2. 15.5.2. Developing Distributed Transactions
        3. 15.5.3. Monitoring Distributed Transactions
      6. 15.6. Summary
    10. 16. Modifying Data
      1. 16.1. Inserting Data
        1. 16.1.1. Inserting One Row of Values
        2. 16.1.2. Inserting a Result Set from Select
        3. 16.1.3. Inserting the Result Set from a Stored Procedure
        4. 16.1.4. Creating a Default Row
        5. 16.1.5. Creating a Table While Inserting Data
      2. 16.2. Updating Data
        1. 16.2.1. Updating a Single Table
        2. 16.2.2. Performing Global Search and Replaces
        3. 16.2.3. Referencing Multiple Tables While Updating Data
      3. 16.3. Deleting Data
        1. 16.3.1. Referencing Multiple Tables While Deleting
        2. 16.3.2. Cascading Deletes
        3. 16.3.3. Alternatives to Physically Deleting Data
      4. 16.4. Returning Modified Data
        1. 16.4.1. Returning Data from an Insert
        2. 16.4.2. Returning Data from an Update
        3. 16.4.3. Returning Data from a Delete
        4. 16.4.4. Returning Data into a @Table Variable
      5. 16.5. Potential Data-Modification Obstacles
        1. 16.5.1. Data Type/Length Obstacles
        2. 16.5.2. Primary Key Obstacles
        3. 16.5.3. Foreign Key Obstacles
        4. 16.5.4. Unique Index Obstacles
        5. 16.5.5. Null and Default Obstacles
        6. 16.5.6. Check Constraint Obstacles
        7. 16.5.7. Instead of Trigger Obstacles
        8. 16.5.8. After Trigger Obstacles
        9. 16.5.9. Calculated Columns
        10. 16.5.10. Non-Updateable View Obstacles
        11. 16.5.11. Views with-check-option Obstacles
        12. 16.5.12. Security Obstacles
      6. 16.6. Summary
  9. III. Developing with SQL Server
    1. 17. Implementing the Physical Database Schema
      1. 17.1. Designing the Physical Database Schema
        1. 17.1.1. Physical Schema Design Options
        2. 17.1.2. Refining the Data Patterns
        3. 17.1.3. Designing for Performance
        4. 17.1.4. Designing for Extensibility
        5. 17.1.5. Responsible Denormalization
      2. 17.2. Creating the Database
        1. 17.2.1. The Create DDL Command
        2. 17.2.2. Database-File Concepts
        3. 17.2.3. Configuring File Growth
        4. 17.2.4. Using Multiple Files
          1. 17.2.4.1. Creating a Database with Multiple Files
          2. 17.2.4.2. Modifying the Files of an Existing Database
        5. 17.2.5. Planning Multiple Filegroups
          1. 17.2.5.1. Creating a Database with Filegroups
          2. 17.2.5.2. Modifying Filegroups
          3. 17.2.5.3. Dropping a Database
      3. 17.3. Creating Tables
        1. 17.3.1. Designing Tables Using Management Studio
        2. 17.3.2. Working with SQL Scripts
        3. 17.3.3. Schemas
        4. 17.3.4. Table and Column Names
        5. 17.3.5. Filegroups
      4. 17.4. Creating Primary Keys
        1. 17.4.1. Primary Keys
          1. 17.4.1.1. Creating Primary Keys
          2. 17.4.1.2. Natural Primary Keys
          3. 17.4.1.3. Identity Column Surrogate Primary Keys
          4. 17.4.1.4. Using Uniqueidentifier Surrogate Primary Keys
        2. 17.4.2. Creating Foreign Keys
          1. 17.4.2.1. Declarative Referential Integrity
          2. 17.4.2.2. Optional Foreign Keys
          3. 17.4.2.3. Cascading Deletes and Updates
      5. 17.5. Creating User-Data Columns
        1. 17.5.1. Column Data Types
          1. 17.5.1.1. Character Data Types
          2. 17.5.1.2. Numeric Data Types
          3. 17.5.1.3. Date/Time Data Types
          4. 17.5.1.4. Other Data Types
        2. 17.5.2. Calculated Columns
        3. 17.5.3. Column Constraints and Defaults
          1. 17.5.3.1. Column Nullability
          2. 17.5.3.2. Unique Constraints
          3. 17.5.3.3. Check Constraints
          4. 17.5.3.4. Default Option
        4. 17.5.4. Data Catalog
          1. 17.5.4.1. User-Defined Rules
          2. 17.5.4.2. User-Defined Default
          3. 17.5.4.3. User-Defined Data Types
      6. 17.6. DDL Triggers
        1. 17.6.1. Creating and Altering DDL Triggers
        2. 17.6.2. EventData()
        3. 17.6.3. Enabling and Disabling DDL Triggers
      7. 17.7. Summary
    2. 18. Programming with Transact-SQL
      1. 18.1. Transact-SQL Fundamentals
        1. 18.1.1. T-SQL Batches
          1. 18.1.1.1. Terminating a Batch
          2. 18.1.1.2. DDL Commands
          3. 18.1.1.3. Switching Databases
          4. 18.1.1.4. Executing Batches
          5. 18.1.1.5. Executing a Stored Procedure
        2. 18.1.2. T-SQL Formatting
          1. 18.1.2.1. Statement Termination
          2. 18.1.2.2. Line Continuation
          3. 18.1.2.3. Comments
        3. 18.1.3. Debugging T-SQL
      2. 18.2. Variables
        1. 18.2.1. Variable Default and Scope
        2. 18.2.2. Using the Set and Select Commands
        3. 18.2.3. Conditional Select
        4. 18.2.4. Using Variables within SQL Queries
        5. 18.2.5. Multiple Assignment Variables
      3. 18.3. Procedural Flow
        1. 18.3.1. If
          1. 18.3.1.1. Begin/End
          2. 18.3.1.2. If Exists()
          3. 18.3.1.3. If/Else
        2. 18.3.2. While
        3. 18.3.3. Goto
      4. 18.4. Examining SQL Server with Code
        1. 18.4.1. sp_help
        2. 18.4.2. Global Variables
      5. 18.5. Temporary Tables and Table Variables
        1. 18.5.1. Local Temporary Tables
        2. 18.5.2. Global Temporary Tables
        3. 18.5.3. Table Variables
      6. 18.6. Dynamic SQL
        1. 18.6.1. Executing Dynamic SQL
        2. 18.6.2. sp_excecuteSQL
        3. 18.6.3. Developing Dynamic SQL Code
      7. 18.7. Error Handling
        1. 18.7.1. Try...Catch
        2. 18.7.2. Legacy @@Error Global Variable
        3. 18.7.3. @@RowCount Global Variable
        4. 18.7.4. Raiserror
          1. 18.7.4.1. The Simple Raiserror Form
          2. 18.7.4.2. The Complete Raiserror Form
          3. 18.7.4.3. Error Severity
          4. 18.7.4.4. Adding Variable Parameters to Messages
          5. 18.7.4.5. Stored Messages
          6. 18.7.4.6. Logging the Error
          7. 18.7.4.7. SQL Server Log
        5. 18.7.5. Catch Block
        6. 18.7.6. T-SQL Fatal Errors
      8. 18.8. Summary
    3. 19. Performing Bulk Operations
      1. 19.1. Bulk Insert
        1. 19.1.1. Bulk Insert Options
        2. 19.1.2. BCP
      2. 19.2. Summary
    4. 20. Kill the Cursor!
      1. 20.1. Anatomy of a Cursor
        1. 20.1.1. The Five Steps to Cursoring
        2. 20.1.2. Managing the Cursor
        3. 20.1.3. Update Cursors
        4. 20.1.4. Cursor Scope
        5. 20.1.5. Cursors and Transactions
      2. 20.2. Cursor Strategies
      3. 20.3. Complex-Logic Solutions
        1. 20.3.1. The Logic Code
        2. 20.3.2. SQL-92 Cursor with Stored Procedure
        3. 20.3.3. Fast-Forward Cursor with Stored Procedure
        4. 20.3.4. Fast-Forward Cursor and User-Defined Function
        5. 20.3.5. Update Cursor with Stored Procedure
        6. 20.3.6. Update Query with User-Defined Function
        7. 20.3.7. Multiple Queries
        8. 20.3.8. Query with Case Expression
        9. 20.3.9. Performance Analysis
      4. 20.4. Denormalizing a List Example
      5. 20.5. Summary
    5. 21. Developing Stored Procedures
      1. 21.1. Managing Stored Procedures
        1. 21.1.1. Create, Alter, and Drop
        2. 21.1.2. Returning a Record Set
        3. 21.1.3. Compiling Stored Procedures
        4. 21.1.4. Stored Procedure Encryption
        5. 21.1.5. System Stored Procedures
      2. 21.2. Passing Data to Stored Procedures
        1. 21.2.1. Input Parameters
        2. 21.2.2. Parameter Defaults
      3. 21.3. Returning Data from Stored Procedures
        1. 21.3.1. Output Parameters
        2. 21.3.2. Using the Return Command
        3. 21.3.3. Path and Scope of Returning Data
      4. 21.4. Using Stored Procedures within Queries
      5. 21.5. Executing Remote Stored Procedures
      6. 21.6. The Complete Stored Procedure
        1. 21.6.1. The pGetPrice Stored Procedure
        2. 21.6.2. The pOrder_AddNew Stored Procedure
        3. 21.6.3. The pOrder_AddItem Stored Procedure
        4. 21.6.4. Adding an Order
      7. 21.7. Summary
    6. 22. Building User-Defined Functions
      1. 22.1. Scalar Functions
        1. 22.1.1. Creating a Scalar Function
        2. 22.1.2. Calling a Scalar Function
        3. 22.1.3. Creating Functions with Schema Binding
      2. 22.2. Inline Table-Valued Functions
        1. 22.2.1. Creating an Inline Table-Valued Function
        2. 22.2.2. Calling an Inline Table-Valued Function
        3. 22.2.3. Using Parameters
        4. 22.2.4. Correlated User Defined Functions
      3. 22.3. Multi-Statement Table-Valued Functions
        1. 22.3.1. Creating a Multi-Statement Table-Valued Function
        2. 22.3.2. Calling the Function
      4. 22.4. Summary
    7. 23. Implementing Triggers
      1. 23.1. Trigger Basics
        1. 23.1.1. Transaction Flow
        2. 23.1.2. Creating Triggers
        3. 23.1.3. After Triggers
        4. 23.1.4. Instead of Triggers
        5. 23.1.5. Trigger Limitations
        6. 23.1.6. Disabling Triggers
        7. 23.1.7. Listing Triggers
        8. 23.1.8. Triggers and Security
      2. 23.2. Working with the Transaction
        1. 23.2.1. Determining the Updated Columns
        2. 23.2.2. Inserted and Deleted Logical Tables
        3. 23.2.3. Developing Multi-Row Enabled–Triggers
      3. 23.3. Multiple-Trigger Interaction
        1. 23.3.1. Trigger Organization
        2. 23.3.2. Nested Triggers
        3. 23.3.3. Recursive Triggers
        4. 23.3.4. Instead of and After Triggers
        5. 23.3.5. Multiple after Triggers
      4. 23.4. Summary
    8. 24. Exploring Advanced T-SQL Solutions
      1. 24.1. Complex Business Rule Validation
      2. 24.2. Complex Referential Integrity
      3. 24.3. Row-Level Custom Security
        1. 24.3.1. The Security Table
          1. 24.3.1.1. Creating the Table
          2. 24.3.1.2. Security Fetch
          3. 24.3.1.3. Assigning Security
          4. 24.3.1.4. Handling Security-Level Updates
        2. 24.3.2. The Security-Check Stored Procedure
        3. 24.3.3. The Security-Check Function
        4. 24.3.4. Using the NT Login
        5. 24.3.5. The Security-Check Trigger
      4. 24.4. Auditing Data Changes
        1. 24.4.1. The Audit Table
        2. 24.4.2. The Fixed Audit Trail Trigger
        3. 24.4.3. Rolling Back from the Audit Trail
        4. 24.4.4. Auditing Complications
          1. 24.4.4.1. Auditing Related Data
          2. 24.4.4.2. Date Created and Date Modified
          3. 24.4.4.3. Auditing Select Statements
          4. 24.4.4.4. Data Auditing and Security
          5. 24.4.4.5. Data Auditing and Performance
        5. 24.4.5. The Dynamic Audit-Trail Trigger and Procedure
      5. 24.5. Transaction-Aggregation Handling
        1. 24.5.1. The Inventory-Transaction Trigger
        2. 24.5.2. The Inventory Trigger
      6. 24.6. Logically Deleting Data
        1. 24.6.1. Logical Delete Triggers
        2. 24.6.2. Undeleting a Logically Deleted Row
        3. 24.6.3. Filtering Out Logically Deleted Rows
        4. 24.6.4. Cascading Logical Deletes
          1. 24.6.4.1. Cascading during the Read
          2. 24.6.4.2. Cascading during the Write
          3. 24.6.4.3. Logical Deletes and Referential Integrity
        5. 24.6.5. Degrees of Inactivity
      7. 24.7. Archiving Data
      8. 24.8. Summary
    9. 25. Creating Extensibility with a Data Abstraction Layer
      1. 25.1. The AddNew Stored Procedure
      2. 25.2. The Fetch Stored Procedure
      3. 25.3. The Update Stored Procedure
        1. 25.3.1. Update with RowVersion
        2. 25.3.2. Minimal-Update
      4. 25.4. The Delete Stored Procedure
      5. 25.5. Summary
    10. 26. Developing for SQL Server Everywhere
      1. 26.1. An Overview of SQL Server 2005 Everywhere Edition
        1. 26.1.1. History
        2. 26.1.2. Concepts
      2. 26.2. What's New in SQL Server 2005 Everywhere Edition
      3. 26.3. Getting Started with SQL Everywhere
        1. 26.3.1. Installing SQL Everywhere
          1. 26.3.1.1. Development Tools
          2. 26.3.1.2. Runtime Tools
          3. 26.3.1.3. Choosing the Correct SQL Everywhere CAB Files
        2. 26.3.2. Query Analyzer 3.0
        3. 26.3.3. Creating a SQL Everywhere Database
          1. 26.3.3.1. Create a SQL Everywhere Database with Managed Code
          2. 26.3.3.2. Create a SQL Everywhere Database from Query Analyzer 3.0
          3. 26.3.3.3. Create a SQL Everywhere Database with Visual Studio 2005
          4. 26.3.3.4. Create a SQL Everywhere Database with SQL Server 2005 Management Studio
          5. 26.3.3.5. Create a SQL Everywhere Database via Merge Replication
        4. 26.3.4. Upgrading an Existing SQL CE 2.0 Database
      4. 26.4. Synchronizing Data
        1. 26.4.1. Remote Data Access
        2. 26.4.2. Merge Replication
        3. 26.4.3. Web Services
      5. 26.5. Packaging and Deployment
        1. 26.5.1. Creating the Database Programmatically
        2. 26.5.2. Deploying a Starter Database with the Mobile Application
        3. 26.5.3. Creating the Database Dynamically through Merge Replication
        4. 26.5.4. Other Approaches
      6. 26.6. Security
        1. 26.6.1. Password Protection
        2. 26.6.2. Encryption
        3. 26.6.3. Secure Data Synchronization
      7. 26.7. Tuning, Maintenance, and Administration
        1. 26.7.1. Measuring and Improving Query Performance
        2. 26.7.2. SQL Everywhere Maintenance
          1. 26.7.2.1. AutoShrink
          2. 26.7.2.2. Compact
          3. 26.7.2.3. AutoFlush
        3. 26.7.3. Repairing a Damaged SQL Everywhere Database
          1. 26.7.3.1. Verify
          2. 26.7.3.2. Repair
        4. 26.7.4. Keeping Merge Replication Healthy
      8. 26.8. More Information
      9. 26.9. Summary
    11. 27. Programming CLR Assemblies within SQL Server
      1. 27.1. .NET Framework Crash Course
        1. 27.1.1. Assemblies
        2. 27.1.2. App Domains
      2. 27.2. Overview of the CLR SQL Server Types
        1. 27.2.1. CLR Integration .NET Type Attributes
        2. 27.2.2. Shared Features of SQL Server CLR Types
        3. 27.2.3. Data Types
      3. 27.3. .NET Methods That Support CLR Integration
      4. 27.4. T-SQL CLR DDL Commands and Catalog Views
        1. 27.4.1. Assemblies
        2. 27.4.2. Database Objects
      5. 27.5. Building Database Types with Visual Studio 2005
        1. 27.5.1. Creating a CLR Project
        2. 27.5.2. Coding a CLR Stored Procedure
        3. 27.5.3. CLR Functions
      6. 27.6. Using the CLR versus Using T-SQL
      7. 27.7. T-SQL Is Not Going Away
      8. 27.8. Summary
    12. 28. Queueing Data with Service Broker
      1. 28.1. Configuring a Message Queue
      2. 28.2. Working with Dialogs
        1. 28.2.1. Sending a Message to the Queue
        2. 28.2.2. Receiving a Message
      3. 28.3. Monitoring Service Broker
      4. 28.4. Summary
    13. 29. Persisting Custom Data Types
      1. 29.1. Creating CLR Integration User-Defined Types
        1. 29.1.1. Satisfying the Requirements
        2. 29.1.2. Coding a CLR UDT with Visual Studio
        3. 29.1.3. Testing and Debugging the UDT
        4. 29.1.4. Performance Considerations
      2. 29.2. CLR Integration UDT Deployment Details
        1. 29.2.1. Strongly Named Assemblies and the GAC
        2. 29.2.2. Creating Strongly Named .NET Assemblies
        3. 29.2.3. Maintaining the UDT
      3. 29.3. Summary
    14. 30. Programming with ADO.NET 2.0
      1. 30.1. An Overview of ADO.NET
        1. 30.1.1. ADO
          1. 30.1.1.1. OLE DB
          2. 30.1.1.2. ADODB Primary Interop Assembly
        2. 30.1.2. The ADO Object Model
          1. 30.1.2.1. OLE DB Data Providers
          2. 30.1.2.2. Mapping Data Types
          3. 30.1.2.3. ADO and Scripting
        3. 30.1.3. ADO.NET
          1. 30.1.3.1. The ADO.NET Object Model
          2. 30.1.3.2. Managed Providers
          3. 30.1.3.3. SQL Native Client
          4. 30.1.3.4. Data Types
          5. 30.1.3.5. DataAdapters and DataSets
      2. 30.2. ADO.NET in Visual Studio 2005
        1. 30.2.1. Server Explorer
        2. 30.2.2. Debugging ADO.NET
        3. 30.2.3. Application Tracing
      3. 30.3. Application Building Basics
        1. 30.3.1. Connecting to SQL Server
        2. 30.3.2. Data Adapters
        3. 30.3.3. DataReaders and Recordsets
        4. 30.3.4. Streams
        5. 30.3.5. Asynchronous Execution
        6. 30.3.6. Using a Single Database Value
        7. 30.3.7. Data Modification
        8. 30.3.8. Binding to Controls
      4. 30.4. Summary
    15. 31. Using XML, XPath, and XQuery
      1. 31.1. XML Data Type
        1. 31.1.1. Casting and Converting
        2. 31.1.2. XML Type Limitations
        3. 31.1.3. XML Schema Collections
      2. 31.2. XML Indexes
      3. 31.3. Querying XML Data
        1. 31.3.1. XPATH
        2. 31.3.2. FLWOR Queries
        3. 31.3.3. Merging XQuery with Select
      4. 31.4. Decomposing XML SQL Server
        1. 31.4.1. Reading XML into SQL Server
        2. 31.4.2. Creating XML with SQL Server 2005
          1. 31.4.2.1. For XML Raw
          2. 31.4.2.2. For XML Auto
      5. 31.5. Summary
    16. 32. Building an SOA Data Store with Web Services
      1. 32.1. HTTP Listening
        1. 32.1.1. About HTTP.sys
        2. 32.1.2. Implicit Endpoints
        3. 32.1.3. Explicit Endpoints
      2. 32.2. WSDL
      3. 32.3. Endpoint Security
      4. 32.4. Summary
    17. 33. InfoPath and SQL Server 2005
      1. 33.1. Overview of InfoPath 2003
        1. 33.1.1. Automatic Data Validation
        2. 33.1.2. Offline Form Completion
        3. 33.1.3. Conditional Formatting
        4. 33.1.4. Security in InfoPath 2003
        5. 33.1.5. InfoPath Object Model
        6. 33.1.6. Scripting and .NET Code
        7. 33.1.7. Note
      2. 33.2. Creating a Form Template
      3. 33.3. Other Features of InfoPath
      4. 33.4. Summary
  10. IV. Enterprise Data Management
    1. 34. Configuring SQL Server
      1. 34.1. Setting the Options
        1. 34.1.1. Configuring the Server
        2. 34.1.2. Configuring the Database
        3. 34.1.3. Configuring the Connection
      2. 34.2. Configuration Options
        1. 34.2.1. Displaying the Advanced Options
        2. 34.2.2. Start/Stop Configuration Properties
          1. 34.2.2.1. Startup Parameters
          2. 34.2.2.2. Startup Stored Procedures
        3. 34.2.3. Memory-Configuration Properties
          1. 34.2.3.1. Dynamic Memory
          2. 34.2.3.2. Reconfigure
          3. 34.2.3.3. Query Wait
          4. 34.2.3.4. AWE Memory
          5. 34.2.3.5. Index Memory
          6. 34.2.3.6. Lock Memory
          7. 34.2.3.7. Max Open Objects
        4. 34.2.4. Processor-Configuration Properties
          1. 34.2.4.1. Affinity Mask
          2. 34.2.4.2. Max Worker Threads
          3. 34.2.4.3. Priority Boost
          4. 34.2.4.4. Lightweight Pooling
          5. 34.2.4.5. Parallelism
        5. 34.2.5. Security-Configuration Properties
          1. 34.2.5.1. Security-Audit Level
          2. 34.2.5.2. C2 Security
        6. 34.2.6. Connection-Configuration Properties
          1. 34.2.6.1. Maximum Concurrent User Connections
          2. 34.2.6.2. Remote Access
          3. 34.2.6.3. Remote Query Timeout
          4. 34.2.6.4. Enforce DTC
          5. 34.2.6.5. Network-Packet Size and Timeout
          6. 34.2.6.6. Max Large-Data-Replication Size
        7. 34.2.7. Server-Configuration Properties
          1. 34.2.7.1. Default Message Language
          2. 34.2.7.2. Full-Text Search Default Language
          3. 34.2.7.3. Allowing Changes to System Tables
          4. 34.2.7.4. Query Governor Cost Limit
          5. 34.2.7.5. Two-Digit-Year Cutoff
        8. 34.2.8. Index-Configuration Properties
        9. 34.2.9. Configuring Database Auto Options
          1. 34.2.9.1. Auto Close
          2. 34.2.9.2. Auto_Shrink
          3. 34.2.9.3. Auto Create Statistics
          4. 34.2.9.4. Auto Update Statistics
        10. 34.2.10. Cursor-Configuration Properties
          1. 34.2.10.1. Cursor Threshold
          2. 34.2.10.2. Cursor Close on Commit
          3. 34.2.10.3. Cursor Default
        11. 34.2.11. SQL ANSI–Configuration Properties
          1. 34.2.11.1. ANSI Null Default
          2. 34.2.11.2. ANSI NULLs
          3. 34.2.11.3. ANSI Padding
          4. 34.2.11.4. ANSI Warnings
          5. 34.2.11.5. Arithmetic Abort
          6. 34.2.11.6. Numeric Round Abort
          7. 34.2.11.7. Concatenation Null Yields Null
          8. 34.2.11.8. Use Quoted Identifier
        12. 34.2.12. Trigger Configuration Properties
          1. 34.2.12.1. Nested Triggers
          2. 34.2.12.2. Recursive Triggers
        13. 34.2.13. Database-State-Configuration Properties
          1. 34.2.13.1. Database-Access Level
          2. 34.2.13.2. Compatibility Level
        14. 34.2.14. Recovery-Configuration Properties
          1. 34.2.14.1. Recovery Model
          2. 34.2.14.2. Torn-Page Detection
      3. 34.3. Summary
    2. 35. Transferring Databases
      1. 35.1. Copy Database Wizard
      2. 35.2. Working with SQL Script
      3. 35.3. Detaching and Attaching
      4. 35.4. Summary
    3. 36. Recovery Planning
      1. 36.1. Recovery Concepts
      2. 36.2. Recovery Models
        1. 36.2.1. Simple Recovery Model
        2. 36.2.2. The Full Recovery Model
        3. 36.2.3. Bulk-Logged Recovery Model
        4. 36.2.4. Setting the Recovery Model
        5. 36.2.5. Modifying Recovery Models
      3. 36.3. Backing Up the Database
        1. 36.3.1. Backup Destination
        2. 36.3.2. Backup Rotation
        3. 36.3.3. Performing Backup with Management Studio
        4. 36.3.4. Backing Up the Database with Code
        5. 36.3.5. Verifying the Backup with Code
      4. 36.4. Working with the Transaction Log
        1. 36.4.1. Inside the Transaction Log
          1. 36.4.1.1. The Active and Inactive Divide
          2. 36.4.1.2. Transaction Checkpoints
        2. 36.4.2. Backing Up the Transaction Log
        3. 36.4.3. Truncating the Log
        4. 36.4.4. The Transaction Log and Simple Recovery Model
      5. 36.5. Recovery Operations
        1. 36.5.1. Detecting the Problem
        2. 36.5.2. Recovery Sequences
        3. 36.5.3. Performing the Restore with Management Studio
        4. 36.5.4. Restoring with T-SQL Code
      6. 36.6. System Databases Recovery
        1. 36.6.1. Master Database
          1. 36.6.1.1. Backing Up the Master Database
          2. 36.6.1.2. Recovering the Master Database
        2. 36.6.2. MSDB System Database
      7. 36.7. Performing a Complete Recovery
      8. 36.8. Summary
    4. 37. Maintaining the Database
      1. 37.1. DBCC Commands
        1. 37.1.1. Database Integrity
          1. 37.1.1.1. Repairing the Database
          2. 37.1.1.2. Multi-User Concerns
          3. 37.1.1.3. Object-Level Validation
          4. 37.1.1.4. Data Integrity
        2. 37.1.2. Index Maintenance
          1. 37.1.2.1. Database Fragmentation
          2. 37.1.2.2. Index Statistics
          3. 37.1.2.3. Index Density
        3. 37.1.3. Database File Size
          1. 37.1.3.1. Monitoring Database File Sizes
          2. 37.1.3.2. Shrinking the Database
          3. 37.1.3.3. Shrinking the Transaction Log
        4. 37.1.4. Miscellaneous DBCC Commands
      2. 37.2. Managing Database Maintenance
        1. 37.2.1. Planning Database Maintenance
        2. 37.2.2. Maintenance Plan Wizard
          1. 37.2.2.1. Adding a Task
          2. 37.2.2.2. Defining the Schedule
          3. 37.2.2.3. Creating New Connections
          4. 37.2.2.4. Logging the Maintenance Progress
        3. 37.2.3. Command-Line Maintenance
        4. 37.2.4. Monitoring Database Maintenance
      3. 37.3. Summary
    5. 38. Automating Database Maintenance with SQL Server Agent
      1. 38.1. Setting Up SQL Server Agent
      2. 38.2. Understanding Alerts, Operators, and Jobs
      3. 38.3. Managing Operators
      4. 38.4. Managing Alerts
        1. 38.4.1. Creating User-Defined Errors
        2. 38.4.2. Creating an Alert
      5. 38.5. Managing Jobs
        1. 38.5.1. Creating a Job Category
        2. 38.5.2. Creating a Job Definition
        3. 38.5.3. Setting Up the Job Steps
        4. 38.5.4. Configuring a Job Schedule
        5. 38.5.5. Handling Completion-, Success-, and Failure-Notification Messages
      6. 38.6. Summary
    6. 39. Replicating Data
      1. 39.1. Why Replicate Data?
        1. 39.1.1. Fault Tolerance/Disaster Recovery
        2. 39.1.2. Application Requirements
        3. 39.1.3. Performance Gains
        4. 39.1.4. Data Distribution
      2. 39.2. Comparing Options for Distributing Data
      3. 39.3. The Microsoft Model
        1. 39.3.1. Publisher
        2. 39.3.2. Subscriber
        3. 39.3.3. Distributor
        4. 39.3.4. Central Publisher
        5. 39.3.5. Central Subscriber
        6. 39.3.6. Republishing
        7. 39.3.7. Peer-to-Peer
        8. 39.3.8. Article
        9. 39.3.9. Push Subscriptions
        10. 39.3.10. Pull Subscriptions
      4. 39.4. Replication Types
        1. 39.4.1. Snapshot Replication
        2. 39.4.2. Snapshot Replication with Immediate Updating
        3. 39.4.3. Snapshot Replication with Queued Updating
        4. 39.4.4. Snapshot Replication with Immediate Updating and Queued Failover
      5. 39.5. Transactional Replication
        1. 39.5.1. Snapshot Agent
        2. 39.5.2. Log Reader Agent
        3. 39.5.3. Distribution Agent
        4. 39.5.4. Peer-to-Peer Replication
        5. 39.5.5. Bi-directional Transactional Replication
        6. 39.5.6. Transactional Replication with Immediate Updating
        7. 39.5.7. Transactional Replication with Queued Updating
        8. 39.5.8. Transactional Replication with Immediate Updating and Queued Failover
        9. 39.5.9. Transactional Replication over the Internet
        10. 39.5.10. Merge Replication
        11. 39.5.11. Merge Replication and SQL CE or SQL Mobile Subscribers
        12. 39.5.12. Merge Replication over the Internet
      6. 39.6. New in SQL 2005 Replication
        1. 39.6.1. Restartable Snapshots
        2. 39.6.2. Oracle Publishing
        3. 39.6.3. Very Tight Security
        4. 39.6.4. Peer-to-Peer Replication Model
        5. 39.6.5. Replicating All DDL
        6. 39.6.6. Replicating Full-Text Indexes
        7. 39.6.7. Allowing Anonymous Subscriptions for All Publications
        8. 39.6.8. Logical Records in Merge Replication
        9. 39.6.9. Precomputed Partitions
        10. 39.6.10. Updates to Unique Keys
        11. 39.6.11. Custom Conflict Handling through SQL RMO
        12. 39.6.12. Numerous Performance Improvements
        13. 39.6.13. Latency Tokens
        14. 39.6.14. Transactional Parallelism
        15. 39.6.15. Download Only Articles
        16. 39.6.16. Replication Monitor
        17. 39.6.17. Merge Replication over HTTPS
        18. 39.6.18. Merge Replication Performance and Scalability Improvements
        19. 39.6.19. SQL RMO
        20. 39.6.20. Simplified Wizards
        21. 39.6.21. Initializing a Subscriber
          1. 39.6.21.1. Backup and Restore
          2. 39.6.21.2. Copy Database
          3. 39.6.21.3. Dynamic Snapshots
      7. 39.7. Configuring Replication
        1. 39.7.1. Using a Local Distributor
        2. 39.7.2. Using a Remote Distributor
      8. 39.8. Creating Snapshot Replication Publications
        1. 39.8.1. Creating Transactional Replication Publications
        2. 39.8.2. Creating Bi-directional Transactional Replication Publications
        3. 39.8.3. Creating Oracle Publications
        4. 39.8.4. Creating Peer-to-Peer Replication Publications
        5. 39.8.5. Creating Merge Replication Publications
        6. 39.8.6. Creating Subscriptions
        7. 39.8.7. Creating Web Synchronizing Subscriptions
      9. 39.9. Monitoring Your Replication Solution
        1. 39.9.1. Agent Profiles
        2. 39.9.2. All Subscriptions
        3. 39.9.3. Warnings and Agents
        4. 39.9.4. Modifying Replication Agent Properties
        5. 39.9.5. Tracer Tokens
      10. 39.10. Replication Performance
      11. 39.11. Replication Troubleshooting
      12. 39.12. Summary
    7. 40. Securing Databases
      1. 40.1. Security Concepts
        1. 40.1.1. Server-Level Security
        2. 40.1.2. Database-Level Security
        3. 40.1.3. Object Ownership
      2. 40.2. Windows Security
        1. 40.2.1. Windows Security
        2. 40.2.2. SQL Server Login
      3. 40.3. Server Security
        1. 40.3.1. SQL Server Authentication Mode
        2. 40.3.2. Windows Authentication
          1. 40.3.2.1. Adding a New Windows Login
          2. 40.3.2.2. Removing a Windows Login
          3. 40.3.2.3. Denying a Windows Login
          4. 40.3.2.4. Setting the Default Database
          5. 40.3.2.5. Orphaned Windows Users
          6. 40.3.2.6. Security Delegation
        3. 40.3.3. SQL Server Logins
          1. 40.3.3.1. Updating a Password
          2. 40.3.3.2. Removing a Login
          3. 40.3.3.3. Setting the Default Database
        4. 40.3.4. Server Roles
      4. 40.4. Database Security
        1. 40.4.1. Guest Logins
        2. 40.4.2. Granting Access to the Database
          1. 40.4.2.1. Granting Access Using T-SQL Code
        3. 40.4.3. Fixed Database Roles
          1. 40.4.3.1. Assigning Fixed Database Roles with Management Studio
          2. 40.4.3.2. Assigning Fixed Database Roles with T-SQL
        4. 40.4.4. Securables Permissions
        5. 40.4.5. Application Roles
      5. 40.5. Object Security
        1. 40.5.1. Object Permissions
          1. 40.5.1.1. Granting Object Permissions with Code
          2. 40.5.1.2. Revoking and Denying Object Permission with Code
        2. 40.5.2. Standard Database Roles
          1. 40.5.2.1. The Public Role
          2. 40.5.2.2. Managing Roles with Code
          3. 40.5.2.3. Hierarchical Role Structures
        3. 40.5.3. Object Security and Management Studio
          1. 40.5.3.1. From the Object List
          2. 40.5.3.2. From the User List
          3. 40.5.3.3. From the Role List
          4. 40.5.3.4. Ownership Chains
        4. 40.5.4. A Sample Security Model Example
      6. 40.6. C2-Level Security
      7. 40.7. Views and Security
      8. 40.8. Cryptography
        1. 40.8.1. Introduction to Cryptography
        2. 40.8.2. The SQL Server-Crypto Hierarchy
        3. 40.8.3. Encrypting with a Passphrase
        4. 40.8.4. Encrypting with a Symmetric Key
          1. 40.8.4.1. Encryption Algorithms
          2. 40.8.4.2. Using the Symmetric Key
          3. 40.8.4.3. Using Asymmetric Keys
          4. 40.8.4.4. Using Certificates
      9. 40.9. Preventing SQL Injection
        1. 40.9.1. Appending Malicious Code
        2. 40.9.2. Or 1=1
        3. 40.9.3. Password? What Password?
        4. 40.9.4. Prevention
      10. 40.10. Summary
    8. 41. Administering SQL Server Express
      1. 41.1. Installing SQL Server Express
        1. 41.1.1. Using Command-Line Parameters to Install SSE Silently
        2. 41.1.2. Using INI Files to Install SSE
      2. 41.2. SQL Server Management Studio Express Edition
      3. 41.3. Summary
  11. V. Business Intelligence
    1. 42. ETL with Integration Services
      1. 42.1. Design Environment
        1. 42.1.1. Connection Managers
        2. 42.1.2. Variables
          1. 42.1.2.1. Variable Usage
          2. 42.1.2.2. Expressions
        3. 42.1.3. Configuring Elements
          1. 42.1.3.1. Control Flow
          2. 42.1.3.2. Control Flow Precedence
          3. 42.1.3.3. Data Flow
        4. 42.1.4. Event Handlers
        5. 42.1.5. Executing a Package in Development
      2. 42.2. Integration Services Package Elements
        1. 42.2.1. Connection Managers
          1. 42.2.1.1. Database
          2. 42.2.1.2. Data Source and Data Source Views
          3. 42.2.1.3. File
          4. 42.2.1.4. Special
        2. 42.2.2. Control Flow Elements
          1. 42.2.2.1. Containers
          2. 42.2.2.2. Control Flow Tasks
          3. 42.2.2.3. Maintenance Plan Tasks
        3. 42.2.3. Data Flow Components
          1. 42.2.3.1. Sources
          2. 42.2.3.2. Destinations
          3. 42.2.3.3. Transformations
      3. 42.3. Maintainable and Manageable Packages
        1. 42.3.1. Logging
        2. 42.3.2. Package Configurations
        3. 42.3.3. Checkpoint Restart
      4. 42.4. Deploying Packages
        1. 42.4.1. Installing Packages
        2. 42.4.2. Executing Packages
      5. 42.5. Summary
    2. 43. Business Intelligence with Analysis Services
      1. 43.1. Data Warehousing
        1. 43.1.1. Star Schema
        2. 43.1.2. Consistency
        3. 43.1.3. Loading Data
          1. 43.1.3.1. Loading Dimensions
          2. 43.1.3.2. Loading Fact Tables
        4. 43.1.4. Analysis Services Quick Start
      2. 43.2. Analysis Services Architecture
        1. 43.2.1. Unified Dimensional Model
        2. 43.2.2. Server
        3. 43.2.3. Client
      3. 43.3. Building a Database
        1. 43.3.1. Business Intelligence Development Studio
        2. 43.3.2. Data Sources
        3. 43.3.3. Data Source View
          1. 43.3.3.1. Creating the Data Source View
          2. 43.3.3.2. Managing the Data Source View
          3. 43.3.3.3. Refining the Data Source View
        4. 43.3.4. Creating a Cube
      4. 43.4. Dimensions
        1. 43.4.1. Dimension Designer
          1. 43.4.1.1. Attributes and Hierarchies
          2. 43.4.1.2. Attribute Source Columns and Ordering
          3. 43.4.1.3. Attribute Relationships
          4. 43.4.1.4. Visibility and Organization
          5. 43.4.1.5. Basic Setup Checklist
        2. 43.4.2. Changing Data in Dimensions
        3. 43.4.3. Beyond Regular Dimensions
          1. 43.4.3.1. Time Dimension
          2. 43.4.3.2. Other Dimension Types
          3. 43.4.3.3. Parent-Child Dimensions
        4. 43.4.4. Dimension Refinements
          1. 43.4.4.1. Hierarchy (All) Level and Default Member
          2. 43.4.4.2. Grouping Dimension Members
      5. 43.5. Cubes
        1. 43.5.1. Cube Structure
          1. 43.5.1.1. Measures
          2. 43.5.1.2. Cube Dimensions
        2. 43.5.2. Dimension Usage
          1. 43.5.2.1. No Relationship
          2. 43.5.2.2. Regular
          3. 43.5.2.3. Fact
          4. 43.5.2.4. Referenced
          5. 43.5.2.5. Many-to-Many
        3. 43.5.3. KPIs
        4. 43.5.4. Actions
        5. 43.5.5. Partitions
          1. 43.5.5.1. Partition Sizing
          2. 43.5.5.2. Creating Partitions
          3. 43.5.5.3. Aggregation Design
        6. 43.5.6. Perspectives
      6. 43.6. Data Storage
        1. 43.6.1. SQL Server Notifications
        2. 43.6.2. Client-Initiated Notifications
        3. 43.6.3. Scheduled Polling Notifications
      7. 43.7. Data Integrity
        1. 43.7.1. Null Processing
        2. 43.7.2. Unknown Member
        3. 43.7.3. Error Configuration
      8. 43.8. Summary
    3. 44. Data Mining with Analysis Services
      1. 44.1. The Data Mining Process
        1. 44.1.1. Modeling with Analysis Services
          1. 44.1.1.1. Data Mining Wizard
          2. 44.1.1.2. Mining Models
          3. 44.1.1.3. Model Evaluation
          4. 44.1.1.4. Deploying
      2. 44.2. Algorithms
        1. 44.2.1. Decision Trees
        2. 44.2.2. Linear Regression
        3. 44.2.3. Clustering
        4. 44.2.4. Sequence Clustering
        5. 44.2.5. Neural Network
        6. 44.2.6. Logistic Regression
        7. 44.2.7. Naive Bayes
        8. 44.2.8. Association Rules
        9. 44.2.9. Time Series
      3. 44.3. OLAP Integration
      4. 44.4. Summary
    4. 45. Programming MDX Queries
      1. 45.1. Basic Select Query
        1. 45.1.1. Cube Addressing
        2. 45.1.2. Dimension Structure
          1. 45.1.2.1. Dimension References
          2. 45.1.2.2. Tuples and Simple Sets
        3. 45.1.3. Basic SELECT Statement
          1. 45.1.3.1. Measures
          2. 45.1.3.2. Generating Sets from Functions
          3. 45.1.3.3. Using SQL Server Management Studio
      2. 45.2. Advanced Select Query
        1. 45.2.1. Subcubes
        2. 45.2.2. WITH Clause
          1. 45.2.2.1. Sets
          2. 45.2.2.2. Calculated Members
        3. 45.2.3. Dimension Considerations
      3. 45.3. MDX Scripting
        1. 45.3.1. Calculated Members and Named Sets
        2. 45.3.2. Adding Business Intelligence
      4. 45.4. Summary
    5. 46. Authoring Reports with Reporting Services
      1. 46.1. Anatomy of a Report
        1. 46.1.1. Report Definition Language (RDL)
        2. 46.1.2. Data Sources
          1. 46.1.2.1. Data Source Types
          2. 46.1.2.2. Common Data Source Connection Strings
          3. 46.1.2.3. Using Expressions in a Connection String
          4. 46.1.2.4. Setting Data Source Credentials
        3. 46.1.3. Reporting Services Data Sets
        4. 46.1.4. Query Parameters and Report Parameters
        5. 46.1.5. Report Content and Layout
      2. 46.2. The Report Authoring Process
        1. 46.2.1. Creating a Reporting Services Project in Visual Studio 2005
        2. 46.2.2. Steps to Create a Report
        3. 46.2.3. Using the Report Wizard to Create Reports
        4. 46.2.4. Authoring a Report from Scratch
          1. 46.2.4.1. Adding a Blank Report
          2. 46.2.4.2. Creating a Shared Data Source
          3. 46.2.4.3. Creating a Data Set
          4. 46.2.4.4. Displaying Data in the Report
          5. 46.2.4.5. Previewing the Report
      3. 46.3. Working with Data
        1. 46.3.1. Working with SQL in the Report Designer
        2. 46.3.2. Using Query Parameters to Select and Filter Data
          1. 46.3.2.1. Nested Report Parameters
          2. 46.3.2.2. Multi-Value Report Parameters
        3. 46.3.3. Adding Calculated Fields to a Data Set
        4. 46.3.4. Working with XML Data Sources
        5. 46.3.5. Working with Expressions
          1. 46.3.5.1. Expression Scope
          2. 46.3.5.2. Express Yourself with Common Expressions
      4. 46.4. Designing the Report Layout
        1. 46.4.1. Design Basics
          1. 46.4.1.1. Designing the Report Header
          2. 46.4.1.2. Designing the Report Footer
          3. 46.4.1.3. Adding and Formatting a Table Report Item
          4. 46.4.1.4. Adding and Formatting a List Report Item
        2. 46.4.2. Using the Table and Matrix Property Pages
        3. 46.4.3. Grouping and Sorting Data in a Table and Matrix
          1. 46.4.3.1. Setting the Sort Order for a Table
          2. 46.4.3.2. Adding Groups to a Table
          3. 46.4.3.3. Formatting Tables with Groups
        4. 46.4.4. Illustrating Data with Charts
        5. 46.4.5. Adding a Pie Chart to a Report
      5. 46.5. Summary
    6. 47. Administering Reports with Reporting Services
      1. 47.1. Deploying Reporting Services Reports
        1. 47.1.1. Deploying Reports Using Visual Studio 2005
          1. 47.1.1.1. Deploying a Single Report or Data Source
          2. 47.1.1.2. Deploying a Reporting Services Project
        2. 47.1.2. Deploying Reports Using the Report Manager
        3. 47.1.3. Deploying Reports Programmatically Using the Reporting Services Web Service
      2. 47.2. Configuring Reporting Services Using the Report Manager
        1. 47.2.1. Configuring Reporting Services Site Settings
          1. 47.2.1.1. Enabling My Reports
          2. 47.2.1.2. Configuring Report History Settings
          3. 47.2.1.3. Configuring Report Execution Settings
        2. 47.2.2. Administering Security
          1. 47.2.2.1. System-Level Roles
          2. 47.2.2.2. Granting System Access to Users and Groups
          3. 47.2.2.3. Item-Level Roles
          4. 47.2.2.4. Controlling Item-level Security
        3. 47.2.3. Working with Linked Reports
        4. 47.2.4. Creating Linked Reports
        5. 47.2.5. Leveraging the Power of Subscriptions
        6. 47.2.6. Creating a Data-Driven Subscription
      3. 47.3. Summary
    7. 48. Analyzing Data with Excel and Data Analyzer
      1. 48.1. Excel PivotTables
        1. 48.1.1. Multidimensional Connections
        2. 48.1.2. Relational Connection
          1. 48.1.2.1. Query Wizard
          2. 48.1.2.2. Microsoft Query
        3. 48.1.3. PivotTable Construction
        4. 48.1.4. PivotChart Construction
      2. 48.2. Excel Data Ranges
      3. 48.3. Data Analyzer
        1. 48.3.1. Creating a View
        2. 48.3.2. Formatting a View
      4. 48.4. Summary
  12. VI. Optimization Strategies
    1. 49. Measuring Performance
      1. 49.1. Measuring Accuracy
      2. 49.2. Using Performance Monitor
        1. 49.2.1. System Monitor
        2. 49.2.2. Counter Logs
      3. 49.3. Using SQL Server Profiler
        1. 49.3.1. Defining a New Trace
        2. 49.3.2. Selecting Events
        3. 49.3.3. Filtering Events
        4. 49.3.4. Organizing Columns
        5. 49.3.5. Using the Profiler Trace
        6. 49.3.6. Integrating Performance Monitor Data
      4. 49.4. Using SQL Trace
      5. 49.5. Using Transact-SQL
        1. 49.5.1. Using Dynamic Management Views
        2. 49.5.2. Using GetDate()
        3. 49.5.3. Using Statistics
      6. 49.6. The Database Performance KPI
        1. 49.6.1. Repeatable Performance Testing
        2. 49.6.2. Production Performance Data
        3. 49.6.3. Scalability Performance Load Testing
      7. 49.7. Summary
    2. 50. Query Analysis and Index Tuning
      1. 50.1. A Holistic Approach to Index Tuning
      2. 50.2. Indexing
        1. 50.2.1. Index Basics
          1. 50.2.1.1. Clustered Indexes
          2. 50.2.1.2. Nonclustered Indexes
        2. 50.2.2. Creating Indexes
          1. 50.2.2.1. Composite Indexes
          2. 50.2.2.2. Primary Keys
          3. 50.2.2.3. Covering Indexes
          4. 50.2.2.4. Filegroup Location
        3. 50.2.3. Index Options
          1. 50.2.3.1. Unique Indexes
          2. 50.2.3.2. Index Fill Factor and Pad Index
          3. 50.2.3.3. Limiting Index Locks and Parallelism
          4. 50.2.3.4. Index Sort Order
          5. 50.2.3.5. The Ignore Dup Key Index Option
          6. 50.2.3.6. The Drop Existing Index Option
          7. 50.2.3.7. The Statistics Norecompute Index Option
          8. 50.2.3.8. Sort in Tempdb
          9. 50.2.3.9. Disabling an Index
        4. 50.2.4. Creating Base Indexes
      3. 50.3. Query Analysis
        1. 50.3.1. Viewing Query Execution Plans
        2. 50.3.2. Using the Showplans
        3. 50.3.3. Interpreting the Query Execution Plan
      4. 50.4. Index Tuning
        1. 50.4.1. Missing Indexes
        2. 50.4.2. Bookmark Lookup
        3. 50.4.3. Optimizable SARGs
        4. 50.4.4. Index Selectivity
      5. 50.5. Reusing Query Execution Plans
      6. 50.6. A Comprehensive Index Strategy
      7. 50.7. Using the Database Engine Tuning Advisor
      8. 50.8. Summary
    3. 51. Managing Transactions, Locking, and Blocking
      1. 51.1. Transactional Basics
      2. 51.2. Transactional Integrity
        1. 51.2.1. The ACID Properties
          1. 51.2.1.1. Atomicity
          2. 51.2.1.2. Consistency
          3. 51.2.1.3. Isolation
          4. 51.2.1.4. Durability
        2. 51.2.2. Transactional Faults
          1. 51.2.2.1. Dirty Reads
          2. 51.2.2.2. Non-Repeatable Reads
          3. 51.2.2.3. Phantom Rows
        3. 51.2.3. Isolation Levels
          1. 51.2.3.1. Level 1—Read Uncommitted
          2. 51.2.3.2. Level 2—Read Committed
          3. 51.2.3.3. Level 3—Repeatable Read
          4. 51.2.3.4. Level 4—Serializable
          5. 51.2.3.5. Snapshot Isolation
          6. 51.2.3.6. Read Committed Snapshot
      3. 51.3. Transaction-Log Architecture
        1. 51.3.1. Transaction Log Sequence
          1. 51.3.1.1. Database Beginning State
          2. 51.3.1.2. Data-Modification Command
          3. 51.3.1.3. Transaction Log Recorded
          4. 51.3.1.4. Transaction Commit
          5. 51.3.1.5. Data-File Update
          6. 51.3.1.6. Transaction Complete
          7. 51.3.1.7. Transaction-Log Rollback
        2. 51.3.2. Transaction-Log Recovery
      4. 51.4. Understanding SQL Server Locking
        1. 51.4.1. Lock Granularity
        2. 51.4.2. Lock Mode
          1. 51.4.2.1. Lock Contention
          2. 51.4.2.2. Shared Lock (S)
          3. 51.4.2.3. Exclusive Lock (X)
          4. 51.4.2.4. Update Lock (U)
          5. 51.4.2.5. Intent Locks
          6. 51.4.2.6. Schema Lock (Sch-M, Sch-S)
        3. 51.4.3. Lock Duration
        4. 51.4.4. Monitoring Locking and Blocking
          1. 51.4.4.1. Using Management Studio
          2. 51.4.4.2. Using Activity Monitor
        5. 51.4.5. Using Profiler
      5. 51.5. Controlling SQL Server Locking
        1. 51.5.1. Setting the Connection Isolation Level
        2. 51.5.2. Using Database Snapshot Isolation
          1. 51.5.2.1. Using Snapshot Isolation
          2. 51.5.2.2. Using Read Committed Snapshot Isolation
          3. 51.5.2.3. Handling Write Conficts
        3. 51.5.3. Using Locking Hints
        4. 51.5.4. Index-Level Locking Restrictions
        5. 51.5.5. Controlling Lock Timeouts
        6. 51.5.6. Evaluating Database Concurrency Performance
        7. 51.5.7. Application Locks
      6. 51.6. Deadlocks
        1. 51.6.1. Creating a Deadlock
        2. 51.6.2. Automatic Deadlock Detection
        3. 51.6.3. Handling Deadlocks
        4. 51.6.4. Minimizing Deadlocks
      7. 51.7. Application Locking Design
        1. 51.7.1. Implementing Optimistic Locking
        2. 51.7.2. Lost Updates
          1. 51.7.2.1. Minimizing Lost Updates
          2. 51.7.2.2. Preventing Lost Updates
      8. 51.8. Transaction Performance Strategies
      9. 51.9. Summary
    4. 52. Providing High Availability
      1. 52.1. Availability Testing
        1. 52.1.1. Warm-Standby Availability
        2. 52.1.2. Log Shipping
          1. 52.1.2.1. The Servers
          2. 52.1.2.2. Configuring Log Shipping with Enterprise Manager
          3. 52.1.2.3. Monitoring Log Shipping
          4. 52.1.2.4. Switching Roles
          5. 52.1.2.5. Configuring a Read-Only Standby Query Server
        3. 52.1.3. Shipping the Users
        4. 52.1.4. Returning to the Original Primary Server
      2. 52.2. Failover Servers and Clustering
      3. 52.3. Failover SQL Server Installs
      4. 52.4. Configuring
      5. 52.5. Database Mirroring
        1. 52.5.1. Prerequisites
        2. 52.5.2. Configuring
      6. 52.6. Architecting the Environment
      7. 52.7. Summary
    5. 53. Scaling Very Large Databases
      1. 53.1. Optimization Theory and Scalability
        1. 53.1.1. Scaling Out the Platform
        2. 53.1.2. Scaling Up the Solution
      2. 53.2. Partitioned Tables and Indexes
        1. 53.2.1. Creating the Partition Function
        2. 53.2.2. Creating Partition Schemes
        3. 53.2.3. Creating the Partition Table
        4. 53.2.4. Querying Partition Tables
        5. 53.2.5. Altering Partition Tables
          1. 53.2.5.1. Merging Partitions
          2. 53.2.5.2. Splitting Partitions
        6. 53.2.6. Switching Tables
          1. 53.2.6.1. Prepping the New Table
          2. 53.2.6.2. Prepping the Partition Table
          3. 53.2.6.3. Performing the Switch
          4. 53.2.6.4. Switching Out
        7. 53.2.7. Rolling Partitions
        8. 53.2.8. Indexing Partitioned Tables
          1. 53.2.8.1. Creating Partitioned Indexes
          2. 53.2.8.2. Maintaining Partitioned Indexes
        9. 53.2.9. Removing Partitioning
      3. 53.3. Working with Indexed Views
        1. 53.3.1. Indexed Views and Queries
        2. 53.3.2. Updating Indexed Views
      4. 53.4. Summary
    6. 54. Designing High-Performance Data Access Providers
      1. 54.1. Data Access Concepts
        1. 54.1.1. Why Good Data Access Matters
        2. 54.1.2. Defining Requirements
      2. 54.2. Data Access Objects
        1. 54.2.1. How the DAO Scenario Works
          1. 54.2.1.1. DAO Address base class
          2. 54.2.1.2. AddressSQLDAO.vb
          3. 54.2.1.3. TestMain.vb
        2. 54.2.2. The Cons of the DAO Pattern
      3. 54.3. Factories
        1. 54.3.1.
          1. 54.3.1.1. Main Factory
          2. 54.3.1.2. SqlServerFactory
          3. 54.3.1.3. MainTester.vb
        2. 54.3.2. The Pros of the Factory Pattern
        3. 54.3.3. The Cons of the Factory Pattern
      4. 54.4. Data Providers
        1. 54.4.1. How a Data Provider Works
          1. 54.4.1.1. Determiner Base Class
          2. 54.4.1.2. SqlOrAccessDeterminer.vb
          3. 54.4.1.3. ProviderDeterminers.dll.config
          4. 54.4.1.4. Providers.dll.config configuration file
          5. 54.4.1.5. DataProvider.vb class
          6. 54.4.1.6. DpAddress.vb Data Provider
          7. 54.4.1.7. DpImplementation.vb Base Class
          8. 54.4.1.8. ImplAddress.vb Base Class
          9. 54.4.1.9. Address.vb - SQL Server Implementation
          10. 54.4.1.10. Data Provider MainTester
        2. 54.4.2. Pros of the Data Provider Pattern
        3. 54.4.3. Cons of the Data Provider Pattern
      5. 54.5. Summary
  13. VII. Appendixes
    1. A. SQL Server 2005 Specifications
    2. B. Sample Databases
      1. B.1. The Sample Database Files
      2. B.2. Cape Hatteras Adventures Version 2
        1. B.2.1. Application Requirements
        2. B.2.2. Database Design
          1. B.2.2.1. Data Conversion
          2. B.2.2.2. CHA2.adp Front End
      3. B.3. OBX Kites
        1. B.3.1. Application Requirements
        2. B.3.2. Database Design
      4. B.4. The Family
        1. B.4.1. Application Requirements
        2. B.4.2. Database Design
      5. B.5. Aesop's Fables
        1. B.5.1. Application Requirements
        2. B.5.2. Database Design