Microsoft® SQL Server 2008 R2 Unleashed

Microsoft® SQL Server 2008 R2 Unleashed

Released September 2010
Publisher(s): Sams
ISBN: 9780768696585

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

This is the industry’s most comprehensive and useful guide to SQL Server 2008 and 2008 R2. It presents start-to-finish coverage of SQL Server’s core database server and management capabilities, plus complete introductions to Integration, Reporting, and Analysis Services, application development, and much more.

Four expert SQL Server administrators, developers, and consultants have packed this book with real-world information, tips, guidelines, and samples drawn from their own extensive experience creating and managing complex database solutions. Writing for intermediate-to-advanced-level SQL Server professionals, they focus on the product’s most complex and powerful capabilities, and its newest tools and features. For example, you’ll find invaluable information on administering SQL Server more efficiently, analyzing and optimizing queries, implementing data warehouses, ensuring high availability, and tuning performance.

The accompanying CD-ROM contains an extraordinary library of practical tools and information including sample databases and all code examples. Whether you’re responsible for SQL Server 2008 analysis, design, implementation, support, administration, or troubleshooting, no other book offers you this much value.

  • Understand the Microsoft SQL Server 2008 environment, R2’s newest features, and each edition’s capabilities

  • Manage SQL Server 2008 more effectively with SQL Server Management Studio, the SQLCMD command-line query tool, and Powershell

  • Efficiently manage security, users, backup/restore, replication, Database Mail, and database objects—from tables and indexes to stored procedures and triggers

  • Increase availability with clustering, database mirroring, and other features

  • Use new Policy-Based Management to centrally configure and operate SQL Server throughout the organization

  • Use SQL Server Profiler to capture queries and identify bottlenecks

  • Improve performance by optimizing queries, design more effective databases, and manage workloads with the new Resource Governor

  • Develop applications using SQL Server 2008’s enhancements to T-SQL and SQLCLR, .NET integration, LINQ to SQL, XML, and XQuery

  • Make the most of Analysis Services, Integration Services, and Reporting Services—especially Microsoft’s new R2 reporting improvements

  • Improve data security using Column-level and Transparent Data Encryption

    •  CD-ROM includes:

  • 15 additional chapters

  • Code samples, scripts, and databases utilized within the book

  • Free version of SQL Shot (performance & tuning software)

    •

    Table of contents

    1. Title Page
    2. Copyright Page
    3. Dedication
    4. Contents at a Glance
    5. Table of Contents
    6. About the Authors
    7. About the Contributing Author
    8. Acknowledgments
    9. We Want to Hear from You!
    10. Reader Services
    11. Introduction
      1. Note
      2. Note
      3. Who This Book Is For
      4. What This Book Covers
      5. Conventions Used in This Book
      6. Note
      7. Note
      8. Good Luck!
    12. Part I. Welcome to Microsoft SQL Server
      1. Chapter 1. SQL Server 2008 Overview
        1. SQL Server Components and Features
          1. The SQL Server Database Engine
          2. SQL Server 2008 Administration and Management Tools
          3. Replication
          4. Database Mirroring
          5. Full-Text Search
          6. SQL Server Integration Services (SSIS)
          7. SQL Server Analysis Services (SSAS)
          8. SQL Server Reporting Services (SSRS)
          9. SQL Server Service Broker
        2. SQL Server 2008 R2 Editions
          1. SQL Server 2008 Standard Edition
          2. SQL Server 2008 Enterprise Edition
          3. Differences Between the Enterprise and Standard Editions of SQL Server
          4. Other SQL Server 2008 Editions
        3. SQL Server Licensing Models
          1. Web Edition Licensing
          2. Developer Edition Licensing
          3. Express Edition Licensing
          4. Compact Edition 3.5 Licensing
          5. Choosing a Licensing Model
          6. Mixing Licensing Models
          7. Passive Server/Failover Licensing
          8. Virtual Server Licensing
          9. Multiple Instances of SQL Server
        4. Summary
      2. Chapter 2. What’s New in SQL Server 2008
        1. New SQL Server 2008 Features
          1. New Storage Features
          2. New Data Types
          3. New Transact-SQL Constructs
          4. New Performance Features
          5. New Security Features
          6. New Database Administration Features
          7. New SQL Server Management Studio Features
          8. PowerShell Integration
          9. New Premium SQL Server Editions
          10. SQL Server Utility for Multiserver Management
          11. PowerPivot for Excel and SharePoint
          12. New Reporting Services Features
        2. SQL Server 2008 Enhancements
          1. SQL Server Management Studio
          2. Dynamic Management Views
          3. Database Mirroring
          4. SQLCLR Enhancements
          5. Replication Enhancements
          6. SQL Server Integration Services Enhancements
          7. Service Broker Enhancements
          8. Analysis Services Enhancements
          9. Installation Enhancements
          10. Deprecated Features
        3. Summary
      3. Chapter 3. Examples of SQL Server Implementations
        1. Application Terms
        2. OLTP Application Examples
          1. OLTP ERP Example
          2. OLTP Shopping Cart Example
        3. DSS Application Examples
          1. DSS Example One
          2. DSS Example Two
          3. DSS Example Three
        4. Summary
    13. Part II. SQL Server Tools and Utilities
      1. Chapter 4. SQL Server Management Studio
        1. What’s New in SSMS
        2. The Integrated Environment
          1. Window Management
          2. Integrated Help
        3. Administration Tools
          1. Registered Servers
          2. Object Explorer
          3. Activity Monitor
          4. Log File Viewer
          5. SQL Server Utility
        4. Development Tools
          1. The Query Editor
          2. Managing Projects in SSMS
          3. Integrating SSMS with Source Control
          4. Using SSMS Templates
          5. T-SQL Debugging
          6. Multiserver Queries
        5. Summary
      2. Chapter 5. SQL Server Command-Line Utilities
        1. Note
        2. Note
        3. What’s New in SQL Server Command-Line Utilities
        4. The sqlcmd Command-Line Utility
          1. Executing the sqlcmd Utility
          2. Using Scripting Variables with sqlcmd
        5. The dta Command-Line Utility
        6. The tablediff Command-Line Utility
        7. The bcp Command-Line Utility
        8. The sqldiag Command-Line Utility
        9. The sqlservr Command-Line Utility
        10. Summary
      3. Chapter 6. SQL Server Profiler
        1. What’s New with SQL Server Profiler
        2. SQL Server Profiler Architecture
        3. Creating Traces
          1. Events
          2. Data Columns
          3. Filters
        4. Executing Traces and Working with Trace Output
        5. Saving and Exporting Traces
          1. Saving Trace Output to a File
          2. Saving Trace Output to a Table
          3. Saving the Profiler GUI Output
          4. Importing Trace Files
          5. Importing a Trace File into a Trace Table
          6. Analyzing Trace Output with the Database Engine Tuning Advisor
        6. Replaying Trace Data
        7. Defining Server-Side Traces
          1. Monitoring Running Traces
          2. Stopping Server-Side Traces
        8. Profiler Usage Scenarios
          1. Analyzing Slow Stored Procedures or Queries
          2. Deadlocks
          3. Identifying Ad Hoc Queries
          4. Identifying Performance Bottlenecks
          5. Monitoring Auto-Update Statistics
          6. Monitoring Application Progress
        9. Summary
    14. Part III. SQL Server Administration
      1. Chapter 7. SQL Server System and Database Administration
        1. What’s New in SQL Server System and Database Administration
        2. System Administrator Responsibilities
        3. System Databases
          1. The master Database
          2. The resource Database
          3. The model Database
          4. The msdb Database
          5. The distribution Database
          6. The tempdb Database
          7. Maintaining System Databases
        4. System Tables
        5. System Views
          1. Compatibility Views
          2. Catalog Views
          3. Information Schema Views
          4. Dynamic Management Views
        6. System Stored Procedures
          1. Useful System Stored Procedures
        7. Summary
      2. Chapter 8. Installing SQL Server 2008
        1. What’s New in Installing SQL Server 2008
        2. Installation Requirements
          1. Hardware Requirements
          2. Software Requirements
        3. Installation Walkthrough
          1. Install Screens, Step by Step
          2. Other Options Available in the SQL Server Installation Center
        4. Installing SQL Server Using a Configuration File
          1. Running an Automated or Manual Install
        5. Installing Service Packs and Cumulative Updates
          1. Installing SP1 from the Command Line
        6. Slipstream Installations
        7. Summary
      3. Chapter 9. Upgrading to SQL Server 2008
        1. What’s New in Upgrading SQL Server
        2. Using the SQL Server Upgrade Advisor (UA)
          1. Getting Started with the UA
          2. The Analysis Wizard
          3. The Report Viewer
        3. Destination: SQL Server 2008 or SQL Server 2008 R2
          1. Side-by-Side Migration
          2. Upgrading In-Place
        4. Upgrading Using a Configuration File
        5. Slipstreaming Upgrades
          1. Upgrading from SQL Server 7 or SQL Server 6.5
        6. Upgrading Other SQL Server Components
          1. Upgrading Analysis Services
          2. Upgrading Reporting Services
        7. Summary
      4. Chapter 10. Client Installation and Configuration
        1. What’s New in Client Installation and Configuration
        2. Client/Server Networking Considerations
          1. Server Network Protocols
          2. The Server Endpoint Layer
          3. The Role of SQL Browser
        3. Client Installation
          1. Installation Requirements
          2. Installing the Client Tools
          3. Installing SNAC
        4. Client Configuration
          1. Client Configuration Using SSCM
          2. Connection Encryption
        5. Client Data Access Technologies
          1. Provider Choices
          2. Driver Choices
          3. Connecting Using the Various Providers and Drivers
          4. General Networking Considerations and Troubleshooting
        6. Summary
      5. Chapter 11. Security and User Administration
        1. What’s New in Security and User Administration
        2. An Overview of SQL Server Security
        3. Authentication Methods
          1. Windows Authentication Mode
          2. Mixed Authentication Mode
          3. Setting the Authentication Mode
        4. Managing Principals
          1. Logins
          2. SQL Server Security: Users
          3. User/Schema Separation
          4. Roles
        5. Managing Securables
        6. Managing Permissions
        7. Managing SQL Server Logins
          1. Using SSMS to Manage Logins
          2. Using T-SQL to Manage Logins
        8. Managing SQL Server Users
          1. Using SSMS to Manage Users
          2. Using T-SQL to Manage Users
        9. Managing Database Roles
          1. Using SSMS to Manage Database Roles
          2. Using T-SQL to Manage Database Roles
        10. Managing SQL Server Permissions
          1. Using SSMS to Manage Permissions
          2. Using T-SQL to Manage Permissions
        11. The Execution Context
          1. Explicit Context Switching
          2. Implicit Context Switching
        12. Summary
      6. Chapter 12. Data Encryption
        1. What’s New in Data Encryption
        2. An Overview of Data Security
        3. An Overview of Data Encryption
        4. SQL Server Key Management
          1. Extensible Key Management
        5. Column-Level Encryption
          1. Encrypting Columns Using a Passphrase
          2. Encrypting Columns Using a Certificate
        6. Transparent Data Encryption
          1. Implementing Transparent Data Encryption
          2. Managing TDE in SSMS
          3. Backing Up TDE Certificates and Keys
          4. Limitations of TDE
        7. Column-Level Encryption Versus Transparent Data Encryption
        8. Summary
      7. Chapter 13. Security and Compliance
        1. Exposure and Risk
        2. Across the Life Cycle
        3. The Security Big Picture
        4. Identity Access Management Components
        5. Compliance and SQL Server
        6. SQL Server Auditing
        7. Setting Up Auditing via T-SQL
        8. SQL Injection Is Easy to Do
        9. Summary
      8. Chapter 14. Database Backup and Restore
        1. What’s New in Database Backup and Restore
        2. Developing a Backup and Restore Plan
        3. Types of Backups
          1. Full Database Backups
          2. Differential Database Backups
          3. Partial Backups
          4. Differential Partial Backups
          5. File and Filegroup Backups
          6. Copy-Only Backups
          7. Transaction Log Backups
        4. Recovery Models
          1. Full Recovery
          2. Bulk-Logged Recovery
          3. Simple Recovery
        5. Backup Devices
          1. Disk Devices
          2. Tape Devices
          3. Network Shares
          4. Media Sets and Families
          5. Creating Backup Devices
        6. Backing Up a Database
          1. Creating Database Backups with SSMS
          2. Creating Database Backups with T-SQL
        7. Backing Up the Transaction Log
          1. Creating Transaction Log Backups with SSMS
          2. Creating Transaction Log Backups with T-SQL
        8. Backup Scenarios
          1. Full Database Backups Only
          2. Full Database Backups with Transaction Log Backups
          3. Differential Backups
          4. Partial Backups
          5. File/Filegroup Backups
          6. Mirrored Backups
          7. Copy-Only Backups
          8. Compressed Backups
          9. System Database Backups
        9. Restoring Databases and Transaction Logs
          1. Restores with T-SQL
          2. Restoring by Using SSMS
          3. Restore Information
        10. Restore Scenarios
          1. Restoring to a Different Database
          2. Restoring a Snapshot
          3. Restoring a Transaction Log
          4. Restoring to the Point of Failure
          5. Restoring to a Point in Time
          6. Online Restores
          7. Restoring the System Databases
        11. Additional Backup Considerations
          1. Frequency of Backups
          2. Using a Standby Server
          3. Snapshot Backups
          4. Considerations for Very Large Databases
          5. Maintenance Plans
        12. Summary
      9. Chapter 15. Database Mail
        1. What’s New in Database Mail
        2. Setting Up Database Mail
          1. Creating Mail Profiles and Accounts
          2. Using T-SQL to Update and Delete Mail Objects
          3. Setting System-wide Mail Settings
          4. Testing Your Setup
        3. Sending and Receiving with Database Mail
          1. The Service Broker Architecture
          2. Sending Email
          3. Receiving Email
        4. Using SQL Server Agent Mail
          1. Job Mail Notifications
          2. Alert Mail Notifications
        5. Related Views and Procedures
          1. Viewing the Mail Configuration Objects
          2. Viewing Mail Message Data
        6. Summary
      10. Chapter 16. SQL Server Scheduling and Notification
        1. Note
        2. What’s New in Scheduling and Notification
        3. Configuring the SQL Server Agent
          1. Configuring SQL Server Agent Properties
          2. Configuring the SQL Server Agent Startup Account
          3. Configuring Email Notification
          4. SQL Server Agent Proxy Account
        4. Viewing the SQL Server Agent Error Log
        5. SQL Server Agent Security
        6. Managing Operators
        7. Managing Jobs
          1. Defining Job Properties
          2. Defining Job Steps
          3. Defining Multiple Jobs Steps
          4. Defining Job Schedules
          5. Defining Job Notifications
          6. Viewing Job History
        8. Managing Alerts
          1. Defining Alert Properties
          2. Defining Alert Responses
        9. Scripting Jobs and Alerts
        10. Multiserver Job Management
          1. Creating a Master Server
          2. Enlisting Target Servers
          3. Creating Multiserver Jobs
        11. Event Forwarding
        12. Summary
      11. Chapter 17. Administering SQL Server 2008 with PowerShell
        1. What’s New with PowerShell
        2. Overview of PowerShell
          1. Start Using PowerShell Now
          2. Common Terminology
          3. Object-Based Functionality
          4. SQL Server Management Objects
          5. WMI
          6. Installing PowerShell
          7. PowerShell Console
          8. Scriptable and Interactive
          9. Default Security
          10. Execution Policy
          11. Profiles
          12. Built-in Help Features
        3. PowerShell Scripting Basics
          1. A Few Basic Cmdlets
          2. Creating a PowerShell Script
          3. Adding Comments
          4. Variables
          5. Escaping Characters
          6. Special Variable $_
          7. Joining Variables and Strings
          8. Passing Arguments
          9. Using Param
          10. Arrays
          11. Operators
          12. Conditional Statements
          13. Functions
          14. Looping Statements
          15. Filtering Cmdlets
          16. Formatting Cmdlets
          17. Dealing with CSV Files
          18. Dealing with Dates and Times
          19. -WhatIf/-Confirm Parameters
        4. PowerShell in SQL Server 2008
          1. Adding PowerShell Support
          2. Accessing PowerShell
          3. SQL Server PowerShell
          4. SQL Provider
          5. SQL Cmdlets
          6. SQL Server Agent Support
        5. Step-By-Step Examples
          1. General Tasks
          2. Scheduling Scripts
          3. Common OS-Related Tasks
          4. SQL Server–Specific Tasks
          5. Using the Provider
          6. Creating a Database Table
          7. Performing a Database Backup
          8. Checking Server Settings
          9. Checking the Database Usage
          10. Getting Table Properties
          11. Cmdlet Example: Invoke-SqlCmd
          12. Cmdlet Example: Invoke-PolicyEvaluation
          13. Joining Columns
          14. Retrieving an Entry
        6. Summary
      12. Chapter 18. SQL Server High Availability
        1. What’s New in High Availability
        2. What Is High Availability?
        3. The Fundamentals of HA
          1. Hardware Factors
          2. Backup Considerations
          3. Operating System Upgrades
          4. Vendor Agreements Followed
          5. Training Kept Up to Date
          6. Quality Assurance Done Well
          7. Standards/Procedures Followed
          8. Server Instance Isolation
        4. Building Solutions with One or More HA Options
          1. Microsoft Cluster Services (MSCS)
          2. SQL Clustering
          3. Data Replication
          4. Log Shipping
          5. Database Mirroring
          6. Combining Failover with Scale-Out Options
        5. Other HA Techniques That Yield Great Results
        6. High Availability from the Windows Server Family Side
          1. Microsoft Virtual Server 2005
          2. Virtual Server 2005 and Disaster Recovery
        7. Summary
      13. Chapter 19. Replication
        1. What’s New in Data Replication
        2. What Is Replication?
        3. The Publisher, Distributor, and Subscriber Magazine Metaphor
          1. Publications and Articles
          2. Filtering Articles
        4. Replication Scenarios
          1. The Central Publisher Replication Model
          2. The Central Publisher with Remote Distributor Replication Model
          3. The Publishing Subscriber Replication Model
          4. The Central Subscriber Replication Model
          5. The Multiple Publishers with Multiple Subscribers Replication Model
          6. The Updating Subscribers Replication Model
          7. The Peer-to-Peer Replication Model
        5. Subscriptions
          1. Anonymous Subscriptions (Pull Subscriptions)
          2. The Distribution Database
        6. Replication Agents
          1. The Snapshot Agent
          2. The Log Reader Agent
          3. The Distribution Agent
          4. The Merge Agent
          5. Other Specialized Agents
        7. Planning for SQL Server Data Replication
          1. Autonomy, Timing, and Latency of Data
          2. Methods of Data Distribution
        8. SQL Server Replication Types
          1. Snapshot Replication
          2. Transactional Replication
          3. Merge Replication
        9. Basing the Replication Design on User Requirements
          1. Data Characteristics
        10. Setting Up Replication
          1. Creating a Distributor and Enabling Publishing
          2. Creating a Publication
          3. Horizontal and Vertical Filtering
          4. Creating Subscriptions
        11. Scripting Replication
        12. Monitoring Replication
          1. Replication Monitoring SQL Statements
          2. Monitoring Replication within SQL Server Management Studio
          3. Troubleshooting Replication Failures
          4. New and Improved Peer-to-Peer Replication
          5. The Performance Monitor
          6. Replication in Heterogeneous Environments
          7. Backup and Recovery in a Replication Configuration
          8. Some Thoughts on Performance
          9. Log Shipping
          10. Data Replication and Database Mirroring for Fault Tolerance and High Availability
        13. Summary
      14. Chapter 20. Database Mirroring
        1. What’s New in Database Mirroring
        2. What Is Database Mirroring?
          1. Copy-on-Write Technology
          2. When to Use Database Mirroring
        3. Roles of the Database Mirroring Configuration
          1. Playing Roles and Switching Roles
          2. Database Mirroring Operating Modes
        4. Setting Up and Configuring Database Mirroring
          1. Getting Ready to Mirror a Database
          2. Creating the Endpoints
          3. Granting Permissions
          4. Creating the Database on the Mirror Server
          5. Identifying the Other Endpoints for Database Mirroring
          6. Configuring Database Mirroring by Using the Wizard
          7. Monitoring a Mirrored Database Environment
          8. Removing Mirroring
        5. Testing Failover from the Principal to the Mirror
        6. Client Setup and Configuration for Database Mirroring
        7. Migrate to Database Mirroring 2008 as Fast as You Can
        8. Using Replication and Database Mirroring Together
        9. Using Database Snapshots from a Mirror for Reporting
        10. Summary
      15. Chapter 21. SQL Server Clustering
        1. What’s New in SQL Server Clustering
        2. How Microsoft SQL Server Clustering Works
          1. Understanding MSCS
          2. Extending MSCS with NLB
          3. How MSCS Sets the Stage for SQL Server Clustering
        3. Installing SQL Server Clustering
          1. Configuring SQL Server Database Disks
          2. Installing Network Interfaces
          3. Installing MSCS
          4. Installing SQL Server
          5. Failure of a Node
          6. The Connection Test Program for a SQL Server Cluster
          7. Potential Problems to Watch Out for with SQL Server Clustering
        4. Summary
      16. Chapter 22. Administering Policy-Based Management
        1. Introduction to Policy-Based Management
        2. Policy-Based Management Concepts
          1. Facets
          2. Conditions
          3. Policies
          4. Categories
          5. Targets
          6. Execution Modes
          7. Central Management Servers
        3. Implementing Policy-Based Management
          1. Creating a Condition Based on a Facet
          2. Creating a Policy
          3. Creating a Category
          4. Evaluating Policies
          5. Importing and Exporting Policies
        4. Sample Templates and Real-World Examples
          1. Sample Policy Templates
          2. Evaluating Recovery Models
          3. Implementing Surface Area Configuration Checks
          4. SQL Server Health Checks
          5. Ensuring Object Naming Conventions
          6. Checking Best Practices Compliance
        5. Policy-Based Management Best Practices
        6. Summary
    15. Part IV. Database Administration
      1. Chapter 23. Creating and Managing Databases
        1. Note
        2. What’s New in Creating and Managing Databases
        3. Data Storage in SQL Server
        4. Database Files
          1. Primary Files
          2. Secondary Files
          3. Using Filegroups
          4. Using Partitions
          5. Transaction Log Files
        5. Creating Databases
          1. Using SSMS to Create a Database
          2. Using T-SQL to Create Databases
        6. Setting Database Options
          1. The Database Options
          2. Using T-SQL to Set Database Options
          3. Retrieving Option Information
        7. Managing Databases
          1. Managing File Growth
          2. Expanding Databases
          3. Shrinking Databases
          4. Moving Databases
          5. Restoring a Database Backup to a New Location
          6. Using ALTER DATABASE
          7. Detaching and Attaching Databases
        8. Summary
      2. Chapter 24. Creating and Managing Tables
        1. What’s New in SQL Server 2008
        2. Creating Tables
          1. Using Object Explorer to Create Tables
          2. Using Database Diagrams to Create Tables
          3. Using T-SQL to Create Tables
        3. Defining Columns
          1. Data Types
          2. Column Properties
        4. Defining Table Location
        5. Defining Table Constraints
        6. Modifying Tables
          1. Using T-SQL to Modify Tables
          2. Using Object Explorer and the Table Designer to Modify Tables
          3. Using Database Diagrams to Modify Tables
        7. Dropping Tables
        8. Using Partitioned Tables
          1. Creating a Partition Function
          2. Creating a Partition Scheme
          3. Creating a Partitioned Table
          4. Adding and Dropping Table Partitions
          5. Switching Table Partitions
        9. Creating Temporary Tables
        10. Summary
      3. Chapter 25. Creating and Managing Indexes
        1. What’s New in Creating and Managing Indexes
        2. Types of Indexes
        3. Clustered Indexes
        4. Nonclustered Indexes
        5. Note
        6. Creating Indexes
        7. Creating Indexes with T-SQL
        8. Tip
        9. Creating Indexes with SSMS
        10. Managing Indexes
        11. Managing Indexes with T-SQL
        12. Note
        13. Tip
        14. Tip
        15. Managing Indexes with SSMS
        16. Tip
        17. Dropping Indexes
        18. Note
        19. Online Indexing Operations
        20. Indexes on Views
        21. Summary
      4. Chapter 26. Implementing Data Integrity
        1. What’s New in Data Integrity
        2. Types of Data Integrity
          1. Domain Integrity
          2. Entity Integrity
          3. Referential Integrity
        3. Enforcing Data Integrity
          1. Implementing Declarative Data Integrity
          2. Implementing Procedural Data Integrity
        4. Using Constraints
          1. The PRIMARY KEY Constraint
          2. The UNIQUE Constraint
          3. The FOREIGN KEY Referential Integrity Constraint
          4. The CHECK Constraint
          5. Creating Constraints
          6. Managing Constraints
        5. Rules
        6. Defaults
          1. Declarative Defaults
          2. Bound Defaults
          3. When a Default Is Applied
          4. Restrictions on Defaults
        7. Summary
      5. Chapter 27. Creating and Managing Views in SQL Server
        1. What’s New in Creating and Managing Views
        2. Definition of Views
        3. Using Views
          1. Simplifying Data Manipulation
          2. Focusing on Specific Data
          3. Abstracting Data
          4. Controlling Access to Data
        4. Creating Views
          1. Creating Views Using T-SQL
          2. Creating Views Using the View Designer
        5. Managing Views
          1. Altering Views with T-SQL
          2. Dropping Views with T-SQL
          3. Managing Views with SSMS
        6. Data Modifications and Views
        7. Partitioned Views
          1. Modifying Data Through a Partitioned View
          2. Distributed Partitioned Views
          3. Creating Indexed Views
          4. Indexed Views and Performance
          5. To Expand or Not to Expand
        8. Summary
      6. Chapter 28. Creating and Managing Stored Procedures
        1. What’s New in Creating and Managing Stored Procedures
        2. Advantages of Stored Procedures
        3. Creating Stored Procedures
          1. Creating Procedures in SSMS
          2. Temporary Stored Procedures
        4. Executing Stored Procedures
          1. Executing Procedures in SSMS
          2. Execution Context and the EXECUTE AS Clause
        5. Deferred Name Resolution
          1. Identifying Objects Referenced in Stored Procedures
        6. Viewing Stored Procedures
        7. Modifying Stored Procedures
          1. Viewing and Modifying Stored Procedures with SSMS
        8. Using Input Parameters
          1. Setting Default Values for Parameters
          2. Passing Object Names as Parameters
          3. Using Wildcards in Parameters
          4. Using Table-Valued Parameters
        9. Using Output Parameters
        10. Returning Procedure Status
        11. Debugging Stored Procedures Using SQL Server Management Studio
        12. Using System Stored Procedures
        13. Startup Procedures
        14. Summary
      7. Chapter 29. Creating and Managing User-Defined Functions
        1. What’s New in SQL Server 2008
        2. Why Use User-Defined Functions?
        3. Types of User-Defined Functions
          1. Scalar Functions
          2. Table-Valued Functions
        4. Creating and Managing User-Defined Functions
          1. Creating User-Defined Functions
          2. Viewing and Modifying User-Defined Functions
          3. Managing User-Defined Function Permissions
        5. Rewriting Stored Procedures as Functions
        6. Creating and Using CLR Functions
          1. Adding CLR Functions to a Database
          2. Deciding Between Using T-SQL or CLR Functions
        7. Summary
      8. Chapter 30. Creating and Managing Triggers
        1. What’s New in Creating and Managing Triggers
        2. Using DML Triggers
          1. Creating DML Triggers
          2. Using AFTER Triggers
          3. Using inserted and deleted Tables
          4. Enforcing Referential Integrity by Using DML Triggers
          5. Cascading Deletes
          6. Cascading Updates
          7. INSTEAD OF Triggers
        3. Using DDL Triggers
          1. Creating DDL Triggers
          2. Managing DDL Triggers
        4. Using CLR Triggers
        5. Using Nested Triggers
        6. Using Recursive Triggers
        7. Summary
      9. Chapter 31. Transaction Management and the Transaction Log
        1. What’s New in Transaction Management
        2. What Is a Transaction?
        3. How SQL Server Manages Transactions
        4. Defining Transactions
          1. AutoCommit Transactions
          2. Explicit User-Defined Transactions
          3. Implicit Transactions
          4. Implicit Transactions Versus Explicit Transactions
        5. Transactions and Batches
        6. Transactions and Stored Procedures
        7. Transactions and Triggers
          1. Triggers and Transaction Nesting
          2. Triggers and Multistatement Transactions
          3. Using Savepoints in Triggers
        8. Transactions and Locking
          1. READ_COMMITTED_SNAPSHOT Isolation
        9. Coding Effective Transactions
        10. Transaction Logging and the Recovery Process
          1. The Checkpoint Process
          2. The Recovery Process
          3. Managing the Transaction Log
        11. Long-Running Transactions
        12. Bound Connections
        13. Distributed Transactions
        14. Summary
      10. Chapter 32. Database Snapshots
        1. What’s New with Database Snapshots
        2. What Are Database Snapshots?
        3. Limitations and Restrictions of Database Snapshots
        4. Copy-on-Write Technology
        5. When to Use Database Snapshots
          1. Reverting to a Snapshot for Recovery Purposes
          2. Safeguarding a Database Prior to Making Mass Changes
          3. Providing a Testing (or Quality Assurance) Starting Point (Baseline)
          4. Providing a Point-in-Time Reporting Database
          5. Providing a Highly Available and Offloaded Reporting Database from a Database Mirror
        6. Setup and Breakdown of a Database Snapshot
          1. Creating a Database Snapshot
          2. Breaking Down a Database Snapshot
        7. Reverting to a Database Snapshot for Recovery
          1. Reverting a Source Database from a Database Snapshot
          2. Using Database Snapshots with Testing and QA
        8. Setting Up Snapshots Against a Database Mirror
          1. Reciprocal Principal/Mirror Reporting Configuration
        9. Database Snapshots Maintenance and Security Considerations
          1. Security for Database Snapshots
          2. Snapshot Sparse File Size Management
          3. Number of Database Snapshots per Source Database
        10. Summary
      11. Chapter 33. Database Maintenance
        1. What Needs to Be Maintained
        2. What’s New in Database Maintenance
        3. The Maintenance Plan Wizard
          1. Backing Up Databases
          2. Checking Database Integrity
          3. Shrinking Databases
          4. Maintaining Indexes and Statistics
          5. Scheduling a Maintenance Plan
        4. Managing Maintenance Plans Without the Wizard
        5. Executing a Maintenance Plan
        6. Maintenance Without a Maintenance Plan
        7. Database Maintenance Policies
        8. Summary
    16. Part V. SQL Server Performance and Optimization
      1. Chapter 34. Data Structures, Indexes, and Performance
        1. What’s New for Data Structures, Indexes, and Performance
        2. Note
        3. Understanding Data Structures
        4. Database Files and Filegroups
          1. Primary Data File
          2. Secondary Data Files
          3. The Log File
          4. File Management
          5. Using Filegroups
          6. FILESTREAM Filegroups
        5. Database Pages
          1. Page Types
          2. Data Pages
          3. Row-Overflow Pages
          4. LOB Data Pages
          5. Index Pages
        6. Space Allocation Structures
          1. Extents
          2. Global and Shared Global Allocation Map Pages
          3. Page Free Space Pages
          4. Index Allocation Map Pages
          5. Differential Changed Map Pages
          6. Bulk Changed Map Pages
        7. Data Compression
          1. Row-Level Compression
          2. Page-Level Compression
          3. The CI Record
          4. Implementing Page Compression
          5. Evaluating Page Compression
          6. Managing Data Compression with SSMS
        8. Understanding Table Structures
          1. Heap Tables
          2. Clustered Tables
          3. Clustered Indexes
          4. Nonclustered Indexes
        9. Data Modification and Performance
          1. Inserting Data
          2. Deleting Rows
          3. Updating Rows
          4. The Statistics Histogram
          5. How the Statistics Histogram Is Used
          6. Index Densities
          7. Estimating Rows Using Index Statistics
          8. Generating and Maintaining Index and Column Statistics
          9. Setting the Fill Factor
          10. Reapplying the Fill Factor
          11. Disabling Indexes
          12. Managing Indexes with SSMS
          13. Clustered Index Indications
          14. Nonclustered Index Indications
          15. Index Covering
          16. Included Columns
          17. Wide Indexes Versus Multiple Indexes
          18. Creating and Using Filtered Indexes
          19. Creating and Using Filtered Statistics
          20. The Database Engine Tuning Advisor
          21. Missing Index Dynamic Management Objects
          22. Missing Index Feature Versus Database Engine Tuning Advisor
        10. Summary
      2. Chapter 35. Understanding Query Optimization
        1. Note
        2. Note
        3. What’s New in Query Optimization
        4. What Is the Query Optimizer?
        5. Query Compilation and Optimization
          1. Compiling DML Statements
          2. Optimization Steps
        6. Query Analysis
          1. Identifying Search Arguments
          2. Identifying OR Clauses
          3. Identifying Join Clauses
          4. Evaluating SARG and Join Selectivity
          5. Estimating Access Path Cost
          6. Using Multiple Indexes
          7. Optimizing with Indexed Views
          8. Optimizing with Filtered Indexes
        7. Join Selection
          1. Join Processing Strategies
          2. Determining the Optimal Join Order
          3. Subquery Processing
        8. Execution Plan Selection
        9. Query Plan Caching
          1. Query Plan Reuse
          2. Query Plan Aging
          3. Recompiling Query Plans
          4. Monitoring the Plan Cache
        10. Other Query Processing Strategies
          1. Predicate Transitivity
          2. Group by Optimization
          3. Queries with DISTINCT
          4. Queries with UNION
        11. Parallel Query Processing
          1. Parallel Query Configuration Options
          2. Identifying Parallel Queries
          3. Parallel Queries on Partitioned Objects
        12. Common Query Optimization Problems
          1. Out-of-Date or Insufficient Statistics
          2. Poor Index Design
          3. Search Argument Problems
          4. Large Complex Queries
          5. Triggers
        13. Managing the Optimizer
          1. Optimizer Hints
          2. Forced Parameterization
          3. Using the USE PLAN Query Hint
          4. Using Plan Guides
          5. Limiting Query Plan Execution with the Query Governor
        14. Summary
      3. Chapter 36. Query Analysis
        1. Note
        2. What’s New in Query Analysis
        3. Query Analysis in SSMS
          1. Execution Plan ToolTips
          2. Logical and Physical Operator Icons
          3. Analyzing Stored Procedures
          4. Saving and Viewing Graphical Execution Plans
          5. Displaying Execution Plan XML
          6. Missing Index Hints
        4. SSMS Client Statistics
        5. Using the SET SHOWPLAN Options
          1. SHOWPLAN_TEXT
          2. SHOWPLAN_ALL
          3. SHOWPLAN_XML
        6. Using sys.dm_exec_query_plan
        7. Query Statistics
          1. STATISTICS IO
          2. STATISTICS TIME
          3. Using datediff() to Measure Runtime
          4. STATISTICS PROFILE
          5. STATISTICS XML
        8. Query Analysis with SQL Server Profiler
        9. Summary
      4. Chapter 37. Locking and Performance
        1. What’s New in Locking and Performance
        2. The Need for Locking
        3. Transaction Isolation Levels in SQL Server
          1. Read Uncommitted Isolation
          2. Read Committed Isolation
          3. Read Committed Snapshot Isolation
          4. Repeatable Read Isolation
          5. Serializable Read Isolation
          6. Snapshot Isolation
        4. The Lock Manager
        5. Monitoring Lock Activity in SQL Server
          1. Querying the sys.dm_tran_locks View
          2. Viewing Locking Activity with SQL Server Profiler
          3. Monitoring Locks with Performance Monitor
        6. SQL Server Lock Types
          1. Shared Locks
          2. Update Locks
          3. Exclusive Locks
          4. Intent Locks
          5. Schema Locks
          6. Bulk Update Locks
        7. SQL Server Lock Granularity
          1. Serialization and Key-Range Locking
          2. Using Application Locks
          3. Index Locking
          4. Row-Level Versus Page-Level Locking
          5. Lock Escalation
        8. Lock Compatibility
        9. Locking Contention and Deadlocks
          1. Identifying Locking Contention
          2. Setting the Lock Timeout Interval
          3. Minimizing Locking Contention
          4. Deadlocks
        10. Table Hints for Locking
          1. Transaction Isolation–Level Hints
          2. Lock Granularity Hints
          3. Lock Type Hints
        11. Optimistic Locking
          1. Optimistic Locking Using the rowversion Data Type
          2. Optimistic Locking with Snapshot Isolation
        12. Summary
      5. Chapter 38. Database Design and Performance
        1. Note
        2. What’s New in Database Design and Performance
        3. Basic Tenets of Designing for Performance
        4. Logical Database Design Issues
          1. Normalization Conditions
          2. Normalization Forms
          3. Benefits of Normalization
          4. Drawbacks of Normalization
        5. Denormalizing a Database
          1. Denormalization Guidelines
          2. Essential Denormalization Techniques
        6. Database Filegroups and Performance
        7. RAID Technology
          1. RAID Level 0
          2. RAID Level 1
          3. RAID Level 10
          4. RAID Level 5
        8. SQL Server and SAN Technology
          1. What Is a SAN?
          2. SAN Considerations for SQL Server
        9. Summary
      6. Chapter 39. Monitoring SQL Server Performance
        1. What’s New in Monitoring SQL Server Performance
        2. Performance Monitoring Tools
          1. The Data Collector and the MDW
          2. SQL Server Utility
          3. SQL Server Extended Events
          4. Windows Performance Monitor
        3. A Performance Monitoring Approach
          1. Monitoring the Network Interface
          2. Monitoring the Processors
          3. Monitoring Memory
          4. Monitoring the Disk System
          5. Monitoring SQL Server’s Disk Activity
          6. Monitoring Other SQL Server Performance Items
        4. Summary
      7. Chapter 40. Managing Workloads with the Resource Governor
        1. Note
        2. Overview of Resource Governor
        3. Resource Governor Components
          1. Classification
          2. Workload Groups
          3. Resource Pools
        4. Configuring Resource Governor
          1. Enabling Resource Governor
          2. Defining Resource Pools
          3. Defining Workload Groups
          4. Creating a Classification Function
        5. Monitoring Resource Usage
        6. Modifying Your Resource Governor Configuration
          1. Deleting Workload Groups
          2. Deleting Resource Pools
          3. Modifying a Classification Function
        7. Summary
      8. Chapter 41. A Performance and Tuning Methodology
        1. The Full Architectural Landscape
        2. Primary Performance and Tuning Handles
        3. A Performance and Tuning Methodology
          1. Designing In Performance and Tuning from the Start
          2. Performance and Tuning for an Existing Implementation
        4. Performance and Tuning Design Guidelines
          1. Hardware and Operating System Guidelines
          2. SQL Server Instance Guidelines
          3. Database-Level Guidelines
          4. Table Design Guidelines
          5. Indexing Guidelines
          6. View Design Guidelines
          7. Transact-SQL Guidelines
          8. Application Design Guidelines
          9. Distributed Data Guidelines
          10. High-Availability Guidelines
        5. Tools of the Performance and Tuning Trade
          1. Microsoft Out-of-the-Box
          2. Third-Party Performance and Tuning Tools
        6. Summary
    17. Part VI. SQL Server Application Development
      1. Chapter 42. What’s New for Transact-SQL in SQL Server 2008
        1. Note
        2. Note
        3. MERGE Statement
          1. MERGE Statement Best Practices and Guidelines
        4. Insert over DML
        5. GROUP BY Clause Enhancements
          1. ROLLUP and CUBE Operator Syntax Changes
          2. GROUPING SETS
          3. The grouping_id() Function
        6. Variable Assignment in DECLARE Statement
        7. Compound Assignment Operators
        8. Row Constructors
        9. New date and time Data Types and Functions
          1. Date and Time Conversions
        10. Table-Valued Parameters
          1. Table-Valued Parameters Versus Temporary Tables
        11. Hierarchyid Data Type
          1. Creating a Hierarchy
          2. Populating the Hierarchy
          3. Querying the Hierarchy
          4. Modifying the Hierarchy
        12. Using FILESTREAM Storage
          1. Enabling FILESTREAM Storage
          2. Setting Up a Database for FILESTREAM Storage
          3. Using FILESTREAM Storage for Data Columns
        13. Sparse Columns
          1. Column Sets
          2. Working with Sparse Columns
          3. Sparse Columns: Good or Bad?
        14. Spatial Data Types
          1. Representing Spatial Data
          2. Working with Geometry Data
          3. Working with Geography Data
          4. Spatial Data Support in SSMS
          5. Spatial Data Types: Where to Go from Here?
        15. Change Data Capture
          1. The Change Data Capture Tables
          2. Enabling CDC for a Database
          3. Enabling CDC for a Table
          4. Querying the CDC Tables
          5. CDC and DDL Changes to Source Tables
        16. Change Tracking
          1. Implementing Change Tracking
          2. Identifying Tracked Changes
          3. Identifying Changed Columns
          4. Change Tracking Overhead
        17. Summary
      2. Chapter 43. Transact-SQL Programming Guidelines, Tips, and Tricks
        1. Note
        2. General T-SQL Coding Recommendations
          1. Provide Explicit Column Lists
          2. Qualify Object Names with a Schema Name
          3. Avoid SQL Injection Attacks When Using Dynamic SQL
          4. Comment Your T-SQL Code
        3. General T-SQL Performance Recommendations
          1. UNION Versus UNION ALL Performance
          2. Use IF EXISTS Instead of SELECT COUNT(*)
          3. Avoid Unnecessary ORDER BY or DISTINCT Clauses
          4. Temp Tables Versus Table Variables Versus Common Table Expressions
          5. Avoid Unnecessary Function Executions
          6. Cursors and Performance
          7. Variable Assignment in UPDATE Statements
        4. T-SQL Tips and Tricks
          1. Date Calculations
          2. Sorting Results with the GROUPING Function
          3. Using CONTEXT_INFO
          4. Working with Outer Joins
          5. Generating T-SQL Statements with T-SQL
          6. Working with @@ERROR and @@ROWCOUNT
          7. De-Duping Data with Ranking Functions
        5. In Case You Missed It: New Transact-SQL Features in SQL Server 2005
        6. The xml Data Type
        7. The max Specifier
        8. TOP Enhancements
        9. The OUTPUT Clause
        10. Common Table Expressions
          1. Recursive Queries with CTEs
        11. Ranking Functions
          1. The ROW_NUMBER Function
          2. The RANK and DENSE_RANK Functions
          3. The NTILE Function
          4. Using Row Numbers for Paging Results
        12. PIVOT and UNPIVOT
        13. The APPLY Operator
          1. CROSS APPLY
          2. OUTER APPLY
        14. TRY...CATCH Logic for Error Handling
        15. The TABLESAMPLE Clause
        16. Summary
      3. Chapter 44. Advanced Stored Procedure Programming and Optimization
        1. T-SQL Stored Procedure Coding Guidelines
          1. Calling Stored Procedures from Transactions
          2. Handling Errors in Stored Procedures
          3. Using Source Code Control with Stored Procedures
        2. Using Cursors in Stored Procedures
          1. Using CURSOR Variables in Stored Procedures
        3. Nested Stored Procedures
          1. Recursive Stored Procedures
        4. Using Temporary Tables in Stored Procedures
          1. Temporary Table Performance Tips
          2. Using the table Data Type
        5. Using Remote Stored Procedures
        6. Stored Procedure Performance
          1. Query Plan Caching
          2. The SQL Server Plan Cache
          3. Shared Query Plans
          4. Automatic Query Plan Recompilation
          5. Forcing Recompilation of Query Plans
        7. Using Dynamic SQL in Stored Procedures
          1. Using sp_executesql
        8. Installing and Using .NET CLR Stored Procedures
          1. Adding CLR Stored Procedures to a Database
          2. T-SQL or CLR Stored Procedures?
        9. Using Extended Stored Procedures
          1. Adding Extended Stored Procedures to SQL Server
          2. Obtaining Information on Extended Stored Procedures
          3. Extended Stored Procedures Provided with SQL Server
          4. Using xp_cmdshell
        10. Summary
      4. Chapter 45. SQL Server and the .NET Framework
        1. What’s New in SQL Server 2008 and the .NET Framework
        2. Getting Comfortable with ADO.NET 3.5 and SQL Server 2008
          1. ADO.NET: Advanced Basics
        3. Developing with LINQ to SQL
          1. Getting Started
          2. Going Deeper
          3. Uncovering LINQ to SQL with Linqpad
        4. Using ADO.NET Data Services
          1. Getting Set Up
          2. Essentials
          3. Building Your Data Service
          4. CRUD Operations
        5. Leveraging the Microsoft Sync Framework
          1. Getting Started with MSF and Sync Services for ADO.NET
          2. Building Our Example OCA
        6. Summary
      5. Chapter 46. SQLCLR: Developing SQL Server Objects in .NET
        1. What’s New for SQLCLR in SQL Server 2008
        2. Developing Custom Managed Database Objects
          1. An Introduction to Custom Managed Database Objects
          2. Managed Object Permissions
          3. Developing Managed Objects with Visual Studio 2008
          4. Developing Managed Stored Procedures
          5. Developing Managed User-Defined Functions (UDFs)
          6. Developing Managed User-Defined Types (UDTs)
          7. Developing Managed User-Defined Aggregates (UDAs)
          8. Developing Managed Triggers
          9. Using Transactions
          10. Using the Related System Catalogs
        3. Summary
      6. Chapter 47. Using XML in SQL Server 2008
        1. What’s New in Using XML in SQL Server 2008
        2. Understanding XML
        3. Relational Data As XML: The FOR XML Modes
          1. RAW Mode
          2. AUTO Mode
          3. EXPLICIT Mode
          4. PATH Mode
          5. FOR XML and the xml Data Type
        4. XML As Relational Data: Using OPENXML
        5. Using the xml Data Type
          1. Defining and Using xml Columns
          2. Using XML Schema Collections
          3. The Built-in xml Data Type Methods
          4. Indexing xml Columns
          5. Full-Text Indexing
        6. Summary
      7. Chapter 48. SQL Server Web Services
        1. What’s New in SQL Server Web Services
        2. Web Services Migration Path
        3. Web Services History and Overview
          1. The Web Services Pattern
        4. Building Web Services
          1. The AS HTTP Keyword Group
          2. The FOR SOAP Keyword Group
        5. Examples: A C# Client Application
          1. Example 1: Running a Web Method Bound to a Stored Procedure from C#
          2. Example 2: Running Ad Hoc T-SQL Batches from a SQL Server Web Service
          3. Example 3: Calling a Web Method–Bound Stored Procedure That Returns XML
        6. Using Catalog Views and System Stored Procedures
        7. Controlling Access Permissions
        8. Summary
      8. Chapter 49. SQL Server Service Broker
        1. What’s New in Service Broker
        2. Understanding Distributed Messaging
          1. The Basics of Service Broker
        3. Designing a Sample System
        4. Understanding Service Broker Constructs
          1. Defining Messages and Choosing a Message Type
          2. Setting Up Contracts for Communication
          3. Creating Queues for Message Storage
          4. Defining Services to Send and Receive Messages
          5. Planning Conversations Between Services
        5. Service Broker Routing and Security
          1. Using Certificates for Conversation Encryption
          2. A Final Note on the Sample System
        6. Troubleshooting SSB Applications with ssbdiagnose.exe
        7. Related System Catalogs
        8. Summary
      9. Chapter 50. SQL Server Full-Text Search
        1. What’s New in SQL Server 2008 Full-Text Search
        2. Upgrade Options in SQL Server 2008
        3. How SQL Server FTS Works
          1. Indexing
          2. Searching
        4. Implementing SQL Server 2008 Full-Text Catalogs
          1. Using T-SQL Commands to Build Full-Text Indexes and Catalogs
          2. Using the Full-Text Indexing Wizard to Build Full-Text Indexes and Catalogs
        5. Full-Text Searches
          1. CONTAINS and CONTAINSTABLE
          2. FREETEXT and FREETEXTTABLE
        6. Full-Text Search Maintenance
        7. Full-Text Search Performance
        8. Full-Text Search Troubleshooting
        9. Summary
    18. Part VII. SQL Server Business Intelligence Features
      1. Chapter 51. SQL Server 2008 Analysis Services
        1. What’s New in SSAS
        2. Understanding SSAS and OLAP
        3. Understanding the SSAS Environment Wizards
          1. OLAP Versus OLTP
        4. An Analytics Design Methodology
          1. An Analytics Mini-Methodology
        5. An OLAP Requirements Example: CompSales International
          1. CompSales International Requirements
          2. OLAP Cube Creation
          3. Using SQL Server BIDS
          4. Creating an OLAP Database
          5. Generating a Relational Database
          6. Cube Perspectives
          7. KPIs
          8. Data Mining
          9. Security and Roles
        6. Summary
      2. Chapter 52. SQL Server Integration Services
        1. What’s New with SSIS
        2. SSIS Basics
        3. SSIS Architecture and Concepts
        4. SSIS Tools and Utilities
        5. A Data Transformation Requirement
        6. Running the SSIS Wizard
        7. The SSIS Designer
        8. The Package Execution Utility
          1. The dtexec Utility
          2. Running Packages
          3. Running Package Examples
          4. The dtutil Utility
          5. dtutil Examples
        9. Connection Projects in Visual Studio
        10. Change Data Capture Addition with R2
        11. Using bcp
          1. Fundamentals of Exporting and Importing Data
          2. File Data Types
          3. Format Files
          4. Using Views
        12. Logged and Nonlogged Operations
          1. Batches
          2. Parallel Loading
          3. Supplying Hints to bcp
        13. Summary
      3. Chapter 53. SQL Server 2008 Reporting Services
        1. What’s New in SSRS 2008
          1. Discontinued Functionality and Breaking Changes
          2. Enhancements
          3. Tool and Service Enhancements
          4. SharePoint Integration Improvements
          5. Service Changes and Improvements
          6. Programming Enhancements
        2. Reporting Services Architecture
        3. Installing and Configuring SSRS
          1. The Installation Sequence
          2. SSRS Configuration Using RSCM
        4. Developing Reports
          1. Tools of the Trade
          2. Report Basics
          3. Overview of the Report Development Process
          4. Data Planning and Preparation
          5. Using Shared Data Sources
          6. Using Datasets
          7. Using Shared Datasets
          8. Developing Reports Using BIDS
          9. Working with the Tablix
          10. Understanding Expressions
          11. Report Design Fundamentals
          12. Using the Data Visualization Controls: Sparkline, Indicator, and Data Bar
          13. Designing Reports Using Report Builder
          14. Report Builder and Report Model Security
          15. Enabling Report Builder
        5. Management and Security
          1. Securing Reports
          2. Subscriptions
          3. Report Execution Options
        6. Performance and Monitoring
          1. SSRS Trace Log
          2. Execution Log
          3. Windows Event Log
          4. Performance Counters
        7. Summary
    19. Part VIII. Bonus Chapters
      1. Chapter 54. Managing Linked and Remote Servers
        1. What’s New in Managing Linked and Remote Servers
        2. Managing Remote Servers
          1. Remote Server Setup
        3. Linked Servers
          1. Distributed Queries
          2. Distributed Transactions
        4. Adding, Dropping, and Configuring Linked Servers
          1. sp_addlinkedserver
          2. sp_linkedservers
          3. sp_dropserver
          4. sp_serveroption
        5. Mapping Local Logins to Logins on Linked Servers
          1. sp_addlinkedsrvlogin
          2. sp_droplinkedsrvlogin
          3. sp_helplinkedsrvlogin
        6. Obtaining General Information About Linked Servers
        7. Executing a Stored Procedure via a Linked Server
        8. Setting Up Linked Servers Using SQL Server Management Studio
        9. Summary
      2. Chapter 55. Configuring, Tuning, and Optimizing SQL Server Options
        1. What’s New in Configuring, Tuning, and Optimizing SQL Server Options
        2. SQL Server Instance Architecture
        3. Configuration Options
        4. Fixing an Incorrect Option Setting
        5. Setting Configuration Options with SSMS
        6. Obsolete Configuration Options
        7. Configuration Options and Performance
          1. access check cache bucket count
          2. access check cache quota
          3. ad hoc distributed queries
          4. affinity I/O mask
          5. affinity mask
          6. Agent XP
          7. awe enabled
          8. backup compression default
          9. blocked process threshold
          10. c2 audit mode
          11. clr enabled
          12. common criteria compliance enabled
          13. cost threshold for parallelism
          14. cross db ownership chaining
          15. cursor threshold
          16. default full-text language
          17. default language
          18. EKM provider enabled
          19. filestream_access_level
          20. fill factor
          21. index create memory
          22. in-doubt xact resolution
          23. lightweight pooling
          24. locks
          25. max degree of parallelism
          26. max server memory and min server memory
          27. max text repl size
          28. max worker threads
          29. min memory per query
          30. nested triggers
          31. network packet size
          32. optimize for ad hoc workloads
          33. PH_timeout
          34. priority boost
          35. query governor cost limit
          36. query wait
          37. recovery interval
          38. remote admin connections
          39. remote login timeout
          40. remote proc trans
          41. remote query timeout
          42. scan for startup procs
          43. show advanced options
          44. user connections
          45. user options
          46. XP-Related Configuration Options
        8. Database Engine Tuning Advisor
          1. The Database Engine Tuning Advisor GUI
          2. The Database Engine Tuning Advisor Command Line
        9. Data Collection Sets
        10. Summary
      3. Chapter 56. SQL Server Disaster Recovery Planning
        1. What’s New in SQL Server Disaster Recovery Planning
        2. How to Approach Disaster Recovery
          1. Disaster Recovery Patterns
          2. Recovery Objectives
          3. A Data-Centric Approach to Disaster Recovery
        3. Microsoft SQL Server Options for Disaster Recovery
          1. Data Replication
          2. Log Shipping
          3. Database Mirroring and Snapshots
        4. The Overall Disaster Recovery Process
          1. The Focus of Disaster Recovery
          2. sqldiag.exe
          3. Planning and Executing a Disaster Recovery
        5. Have You Detached a Database Recently?
        6. Third-Party Disaster Recovery Alternatives
        7. Summary
    20. Footnotes
      1. Chapter 47
    21. Index

    Product information

    • Title: Microsoft® SQL Server 2008 R2 Unleashed
    • Author(s):
    • Release date: September 2010
    • Publisher(s): Sams
    • ISBN: 9780768696585