Microsoft® SQL Server 2005 Unleashed

Microsoft® SQL Server 2005 Unleashed

Released April 2007
Publisher(s): Sams
ISBN: 0672328240

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

Microsoft SQL Server 2005 Unleashed offers comprehensive coverage of SQL Server 2005 that goes beyond the basic syntax and information you’ll find in the product manuals, providing in-depth information derived from the authors’ real-world experience to help you build upon your working knowledge of the product and take your experience and knowledge to a higher level. This book focuses primarily on the information needed by system and database administrators, as well as for users with overlapping duties as both DBA and developer. Included is extensive coverage of the new features and upgraded tools and capabilities of SQL Server 2005, including .NET Framework integration, Integration Services, Service Broker, Database Mirroring and Snapshots, and Reporting Services.

Detailed information on…

  • Installing, upgrading, and administering SQL Server 2005

  • Database maintenance, backup, and recovery

  • Creating and managing tables, views, stored procedures, triggers, and user-defined functions

  • Database and index design

  • SQL Server query optimization, and performance monitoring and tuning

  • Transactions, transaction management, and distributed transactions

  • SQL Server Management Studio—NEW!

  • SQL Server Notification Services; Integration Services—NEW!; Analysis Services; Reporting Services—NEW!; Web Services—NEW!

  • SQL Server Service Broker—NEW!

  • SQL Server and Microsoft .NET Framework integration, including CLR-based stored procedures, functions, and triggers—NEW!

  • SQL Server high availability and SQL Server clustering and replication

  • Database Mirroring—NEW!

  • Database Snapshots—NEW!

  • Using XML in SQL Server 2005

  • SQL Server tools and utilities

    • CD-ROM includes

  • 8 bonus chapters covering topics such as Notification Services, Service Broker, and Full-Text Search

  • Code samples, scripts, and sample databases

  • A PDF version of the entire book

    • Introduction

    Part I Welcome to Microsoft SQL Server

    1 SQL Server 2005 Overview

    2 What’s New in SQL Server 2005

    Part II SQL Server Tools and Utilities

    3 SQL Server Management Studio

    4 SQL Server Command-Line Utilities

    5 SQL Server Profiler

    Part III SQL Server Administration

    6 SQL Server System and Database Administration

    7 Installing SQL Server 2005

    8 Upgrading to SQL Server 2005

    9 Client Installation and Configuration

    10 Security and User Administration

    11 Database Backup and Restore

    12 Database Mail

    13 SQL Server Scheduling and Notification

    14 SQL Server High Availability.

    15 Replication

    16 Database Mirroring

    17 SQL Server Clustering

    Part IV Database Administration

    18 Creating and Managing Databases

    19 Creating and Managing Tables

    20 Creating and Managing Indexes

    21 Implementing Data Integrity

    22 Creating and Managing Views in SQL Server

    23 Creating and Managing Stored Procedures

    24 Creating and Managing User-Defined Functions

    25 Creating and Managing Triggers

    26 Transaction Management and the Transaction Log

    27 Database Snapshots

    28 Database Maintenance

    Part V SQL Server Performance and Optimization

    29 Indexes and Performance

    30 Understanding Query Optimization

    31 Query Analysis

    32 Locking and Performance

    33 Database Design and Performance

    34 Monitoring SQL Server Performance

    Part VI SQL Server Application Development

    35 What’s New for Transact-SQL in SQL Server 2005

    36 SQL Server and the .NET Framework

    37 Using XML in SQL Server 2005

    38 SQL Server Web Services

    Part VII SQL Server Business Intelligence Features

    39 SQL Server 2005 Analysis Services

    40 SQL Server Integration Services

    41 SQL Server 2005 Reporting Services

    Bonus Chapters on the CD

    42 Managing Linked and Remote Servers

    43 Configuring, Tuning, and Optimizing SQL Server Options

    44 Administering Very Large SQL Server Databases

    45 SQL Server Disaster Recovery Planning

    46 Transact-SQL Programming Guidelines, Tips, and Tricks

    47 SQL Server Notification Services

    48 SQL Server Service Broker

    49 SQL Server Full-Text Search

    Index

    Table of contents

    1. Copyright
      1. Dedication
    2. About the Lead Authors
      1. About the Contributing Authors
    3. Acknowledgments
    4. We Want to Hear from You!
      1. Reader Services
    5. Introduction
      1. Who This Book Is For
      2. What This Book Covers
      3. Conventions Used in This Book
      4. Good Luck!
    6. I. Welcome to Microsoft SQL Server
      1. 1. SQL Server 2005 Overview
        1. SQL Server Components and Features
          1. The SQL Server Database Engine
            1. Reliable Storage
            2. Rapid Data Access
            3. Consistent Data Access
            4. Access Control
            5. Data Integrity
          2. SQL Server 2005 Administration and Management Tools
            1. SQL Server Management Studio (SSMS)
            2. The SQL Server Surface Area Configuration Tool
            3. SQL Server Configuration Manager
            4. SQL Server Agent
            5. SQL Server Profiler
          3. Replication
            1. Snapshot Replication
            2. Transactional Replication
            3. Merge Replication
            4. Immediate Updating
          4. Database Mirroring
          5. Full-Text Search
          6. SQL Server Integration Services (SSIS)
          7. SQL Server Analysis Services (SSAS)
          8. SQL Server 2005 Reporting Services
          9. SQL Server Notification Services
          10. SQL Server Service Broker
        2. SQL Server 2005 Editions
          1. SQL Server 2005 Standard Edition
          2. SQL Server 2005 Enterprise Edition
          3. Differences Between the Enterprise and Standard Editions of SQL Server
          4. Other SQL Server 2005 Editions
            1. Workgroup Edition
            2. Developer Edition
            3. Express Edition
            4. Mobile Edition
        3. SQL Server Licensing Models
          1. Developer Edition Licensing
          2. Express Edition Licensing
          3. Mobile Edition Licensing
          4. Choosing a Licensing Model
          5. Mixing Licensing Models
          6. Passive Server/Failover Licensing
          7. Virtual Server Licensing
        4. Summary
      2. 2. What’s New in SQL Server 2005
        1. New SQL Server 2005 Features
          1. SQL Server Management Studio
          2. SQL Server Configuration Manager
          3. CLR/.NET Framework Integration
          4. Dynamic Management Views
          5. System Catalog Views
          6. SQL Server Management Objects
          7. Dedicated Administrator Connection
          8. SQLCMD
          9. Database Mail
          10. Online Index and Restore Operations
          11. Native Encryption
          12. Database Mirroring
          13. Database Snapshots
          14. Service Broker
          15. SQL Server Integration Services
          16. Table and Index Partitioning
          17. Snapshot Isolation
          18. Business Intelligence Development Studio
          19. Query Notification
          20. Multiple Active Result Sets
          21. New SQL Server Data Types
            1. The xml Data Type
            2. varchar(max) and varbinary(max)
        2. SQL Server 2005 Enhancements
          1. Database Engine Enhancements
          2. Index Enhancements
          3. T-SQL Enhancements
          4. Security Enhancements
          5. Backup and Restore Enhancements
          6. SQL Server Agent Enhancements
          7. Recovery Enhancements
          8. Replication Enhancements
          9. Failover Clustering Enhancements
          10. Notification Services Enhancements
          11. Full-Text Search Enhancements
          12. Web Services Enhancements
          13. Analysis Services Enhancements
          14. Reporting Services Enhancements
        3. Summary
    7. II. SQL Server Tools and Utilities
      1. 3. SQL Server Management Studio
        1. What’s New in SSMS
        2. The Integrated Environment
          1. Window Management
          2. Integrated Help
        3. Administration Tools
          1. Using Registered Servers
          2. Using Object Explorer
          3. Using Activity Monitor
          4. Using Log File Viewer
        4. Development Tools
          1. The Query Editor
            1. Query Editor Types
            2. Disconnected Editing
            3. Editing SQLCMD Scripts in SSMS
            4. Regular Expressions and Wildcards in SSMS
            5. Enhanced Performance Output
            6. Using the Query Designer in the Query Editor
          2. Managing Projects in SSMS
          3. Integrating SSMS with Source Control
          4. Using SSMS Templates
        5. Summary
      2. 4. SQL Server Command-Line Utilities
        1. What’s New in SQL Server Command-Line Utilities
        2. The sqlcmd Command-Line Utility
          1. Executing the sqlcmd utility
          2. Using scripting variables with sqlcmd
        3. The dta Command-Line Utility
        4. The tablediff Command-Line Utility
        5. The sac Command-Line Utility
        6. The bcp Command-Line Utility
        7. The sqldiag Command-Line Utility
        8. The sqlservr Command-Line Utility
        9. Removed or Deprecated Utilities in SQL Server 2005
        10. Summary
      3. 5. 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
    8. III. SQL Server Administration
      1. 6. 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. 7. Installing SQL Server 2005
        1. What’s New in Installing SQL Server 2005
        2. Installation Requirements
          1. Hardware Requirements
          2. Software Requirements
            1. Windows Service Requirements
            2. Network Protocol Support
            3. Failover Cluster and Windows Vista Support
            4. How to Handle Previously Installed Community Technology Preview (CTP) Editions of SQL Server 2005
            5. Running Multiple Simultaneous Editions
        3. Installation Walkthrough
          1. Install Screens, Step-by-Step
            1. Viewing the Installation Log Files
            2. Installation Paths
        4. Unattended Installation
          1. Remote Installation
        5. Installing SP1
          1. Unattended SP1 Installation
        6. Summary
      3. 8. Upgrading to SQL Server 2005
        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 2005
          1. Side-by-Side Migration
            1. Avoiding an Unintentional In-Place Upgrade During Setup
            2. Using the SQL Server Client Tools
            3. Migrating Databases
              1. The Copy Database Wizard
              2. Database Compatibility Levels
            4. Migrating Analysis Services
            5. Migrating DTS Packages
            6. Migrating Reporting Services
            7. Migrating Notification Services
          2. Upgrading In-Place
            1. The SQL Server 2005 Upgrade Matrix
            2. Other Installed, Upgraded, and Discontinued Components
            3. Upgrading Databases
            4. Upgrading Analysis Services
            5. Upgrading DTS
            6. Upgrading Reporting Services
            7. Upgrading Notification Services
        4. Unattended Upgrades
        5. Summary
      4. 9. 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
            1. Client Access Provisioning
          3. The Role of SQL Browser
            1. Ports, Pipes, and Instances
        3. Client Installation
          1. Installation Requirements
          2. Installing the Client Tools
          3. Installing SNAC
            1. Redistributing SNAC with Custom Client Applications
        4. Client Configuration
          1. Client Configuration Using SSCM
            1. Server Aliases
          2. Connection Encryption
        5. Client Data Access Technologies
          1. Provider Choices
          2. Driver Choices
          3. Connecting Using the Various Providers and Drivers
            1. Using SNAC
              1. Using OLE DB with SNAC
              2. Using ODBC with SNAC
              3. Using ADO with SNAC
            2. Using the .NET Framework Data Provider for SQL Server
              1. The SQLCLR Context Connection
            3. Using MDAC
              1. Using ODBC with MDAC
              2. Using OLE DB with MDAC
            4. Using JDBC
          4. General Networking Considerations and Troubleshooting
            1. Firewall Considerations
            2. Tools for Testing Connections
        6. Summary
      5. 10. 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
            1. The dbo User
            2. The guest User
            3. The INFORMATION_SCHEMA User
            4. The sys User
          3. User/Schema Separation
          4. Roles
            1. Fixed-Server Roles
            2. Fixed-Database Roles
              1. The public Role
            3. User-Defined Roles
            4. Application 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
            1. Using SSMS to Manage Permissions at the Server Level
            2. Using SSMS to Manage Permissions at the Database Level
            3. Using SSMS to Manage Permissions at the Object Level
          2. Using T-SQL to Manage Permissions
        11. The Execution Context
          1. Explicit Context Switching
          2. Implicit Context Switching
        12. Summary
      6. 11. 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
            1. The NO_TRUNCATE Option
            2. The NORECOVERY | STANDBY= undo_file_name Options
            3. The NO_LOG | TRUNCATE_ONLY Options
        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. System Database Backups
        9. Restoring Databases and Transaction Logs
          1. Restores with T-SQL
            1. Database Restores with T-SQL
            2. Transaction Log Restores with T-SQL
          2. Restoring by Using SSMS
          3. Restore Information
            1. The RESTORE FILELISTONLY Command
            2. The RESTORE HEADERONLY Command
            3. The RESTORE VERIFYONLY Command
            4. Backing Up and Restoring System Tables
        10. Restore Scenarios
          1. Restoring to a Different Database
          2. Restoring a Transaction Log
          3. Restoring to the Point of Failure
            1. Backing Up the Tail of the Transaction Log
            2. Recovering the Full Database Recovery
            3. Restoring the Transaction Log Backup
          4. Restoring to a Point in Time
          5. Online Restores
          6. 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
      7. 12. 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 Systemwide 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
            1. Creating an Operator
            2. Enabling SQL Agent Mail
            3. Creating the Job
            4. Testing the Job-Completion Notification
          2. Alert Mail Notifications
            1. Creating an Alert
            2. Testing the Alert Notification
        5. Related Views and Procedures
          1. Viewing the Mail Configuration Objects
          2. Viewing Mail Message Data
        6. Summary
      8. 13. SQL Server Scheduling and Notification
        1. What’s New in Scheduling and Notification
        2. 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
        3. Viewing the SQL Server Agent Error Log
        4. SQL Server Agent Security
        5. Managing Operators
        6. Managing Jobs
          1. Defining Job Properties
          2. Defining Job Steps
            1. Defining Multiple Jobs Steps
          3. Defining Job Schedules
          4. Defining Job Notifications
          5. Viewing Job History
        7. Managing Alerts
          1. Defining Alert Properties
          2. Defining Alert Responses
        8. Scripting Jobs and Alerts
        9. Multiserver Job Management
          1. Creating a Master Server
          2. Enlisting Target Servers
          3. Creating Multiserver Jobs
        10. Event Forwarding
        11. Summary
      9. 14. SQL Server High Availability
        1. What’s New in High Availability
        2. What Is High Availability?
        3. The Fundamentals of HA
          1. Hardware
          2. Backup
          3. Operating System
          4. Vendor Agreements
          5. Training
          6. Quality Assurance
          7. Standards/Procedures
          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
      10. 15. Replication
        1. What’s New in Data Replication
        2. What Is Replication?
        3. The Publisher, Distributor, and Subscriber 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 or 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
            1. The Snapshot Agent Synchronizaton
            2. Snapshot Agent Processing
          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
            1. Preparing for 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. The Performance Monitor
          5. Replication in Heterogeneous Environments
          6. Backup and Recovery in a Replication Configuration
          7. Some Thoughts on Performance
          8. Log Shipping
          9. Data Replication and Database Mirroring for Fault Tolerance and High Availability
        13. Summary
      11. 16. 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. Identifying the Other Endpoints for Database Mirroring
          5. Creating the Database on the Mirror Server
          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. Using Replication and Database Mirroring Together
        8. Using Database Snapshots from a Mirror for Reporting
        9. Summary
      12. 17. 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
    9. IV. Database Administration
      1. 18. Creating and Managing Databases
        1. What’s New in Creating and Managing Databases
        2. Data Storage in SQL Server
        3. Database Files
          1. Primary Files
          2. Secondary Files
          3. Using Filegroups
          4. Using Partitions
          5. Transaction Log Files
            1. How the Transaction Log Works
        4. Creating Databases
          1. Using SSMS to Create a Database
          2. Using T-SQL to Create Databases
        5. Setting Database Options
          1. The Database Options
          2. Using T-SQL to Set Database Options
          3. Retrieving Option Information
        6. Managing Databases
          1. Managing File Growth
          2. Expanding Databases
          3. Shrinking Databases
            1. Using DBCC SHRINKDATABASE to Shrink Databases
            2. Using DBCC SHRINKFILE to Shrink Databases
            3. Shrinking the Log File
            4. Using SSMS to Shrink Databases
          4. Moving Databases
          5. Restoring a Database Backup to a New Location
          6. Using ALTER DATABASE
          7. Detaching and Attaching Databases
        7. Summary
      2. 19. Creating and Managing Tables
        1. What’s New in SQL Server 2005
        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
            1. New Large-Value Data Types
            2. Large Row Support
            3. The xml Data Type
            4. User-Defined Data Types
            5. CLR User-Defined Types
          2. Column Properties
            1. The NULL and NOT NULL Keywords
            2. Identity Columns
            3. ROWGUIDCOL Columns
            4. Computed Columns
        4. Defining Table Location
        5. Defining Table Constraints
        6. Modifying Tables
          1. Using T-SQL to Modify Tables
            1. Changing a Column Property
            2. Adding and Dropping Columns
          2. Using Object Explorer and the Table Designer to Modify Tables
          3. Using Database Diagrams to Modify Tables
        7. Dropping Tables
        8. Partitioned Tables
          1. Creating a Partition Function
          2. Creating a Partition Scheme
          3. Creating a Partitioned Table
          4. Adding and Dropping Table Partitions
            1. Adding a Table Partition
            2. Dropping a Table Partition
          5. Switching Table Partitions
        9. Creating Temporary Tables
        10. Summary
      3. 20. Creating and Managing Indexes
        1. What’s New in Creating and Managing Indexes
        2. Types of Indexes
          1. Clustered Indexes
          2. Nonclustered Indexes
        3. Creating Indexes
          1. Creating Indexes with T-SQL
          2. Creating Indexes with SSMS
        4. Managing Indexes
          1. Managing Indexes with T-SQL
          2. Managing Indexes with SSMS
        5. Dropping Indexes
        6. Online Indexing Operations
        7. Indexes on Views
        8. Summary
      4. 21. 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
            1. Cascading Referential Integrity
          4. The CHECK Constraint
          5. Creating Constraints
            1. Using T-SQL to Create Constraints
            2. Using SSMS to Create Constraints
          6. Managing Constraints
            1. Gathering Constraint Information
            2. Dropping Constraints
            3. Disabling Constraints
        5. Rules
        6. Defaults
          1. Declarative Defaults
          2. Bound Defaults
          3. When a Default Is Applied
          4. Restrictions on Defaults
        7. Summary
      5. 22. 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. Data Abstraction
          4. Controlling Access to Data
        4. Creating Views
          1. Creating Views Using T-SQL
            1. ENCRYPTION
            2. SCHEMABINDING
            3. VIEW_METADATA
            4. WITH CHECK OPTION
          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
        8. Indexed Views
          1. Creating Indexed Views
          2. Indexed Views and Performance
          3. To Expand or Not to Expand
        9. Summary
      6. 23. 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
            1. Creating Custom Stored Procedure Templates
          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. 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
        9. Using Output Parameters
        10. Returning Procedure Status
        11. Using Cursors in Stored Procedures
          1. Using CURSOR Variables in Stored Procedures
        12. Nested Stored Procedures
          1. Recursive Stored Procedures
        13. Using Temporary Tables in Stored Procedures
          1. Temporary Table Performance Tips
          2. Using the table Data Type
        14. Using Remote Stored Procedures
        15. Debugging Stored Procedures Using Microsoft Visual Studio .NET
        16. Using System Stored Procedures
        17. Stored Procedure Performance
          1. Query Plan Caching
          2. The SQL Server Procedure Cache
          3. Shared Query Plans
          4. Automatic Query Plan Recompilation
            1. Monitoring Stored Procedure Recompilation
          5. Forcing Recompilation of Query Plans
            1. Using sp_recompile
        18. Using Dynamic SQL in Stored Procedures
          1. Using sp_executesql
            1. Using Output Parameters with sp_executesql
        19. Startup Procedures
        20. 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
        21. Creating and Using CLR Stored Procedures
          1. Adding CLR Stored Procedures to a Database
          2. T-SQL or CLR Stored Procedures?
        22. 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
        23. Summary
      7. 24. Creating and Managing User-Defined Functions
        1. What’s New in SQL Server 2005
        2. Why Use User-Defined Functions?
        3. Types of User-Defined Functions
          1. Scalar Functions
          2. Table-Valued Functions
            1. Inline Table-Valued Functions
            2. Multistatement Table-Valued Functions
        4. Creating and Managing User-Defined Functions
          1. Creating User-Defined Functions
            1. Creating T-SQL Functions
            2. Using SSMS to Create Functions
            3. Creating Custom Function Templates
          2. Viewing and Modifying User-Defined Functions
            1. Using T-SQL to View Functions
            2. Using T-SQL to Modify Functions
            3. Using SSMS to View and Modify Functions
          3. Managing User-Defined Function Permissions
        5. Systemwide Table-Valued Functions
        6. Rewriting Stored Procedures as Functions
        7. Creating and Using CLR Functions
          1. Adding CLR Functions to a Database
          2. Deciding Between Using T-SQL or CLR Functions
        8. Summary
      8. 25. 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
            1. Executing AFTER Triggers
            2. Specifying Trigger Firing Order
            3. Special Considerations with AFTER Triggers
          3. Using inserted and deleted Tables
            1. Checking for Column Updates
          4. Enforcing Referential Integrity by Using DML Triggers
          5. Cascading Deletes
          6. Cascading Updates
          7. INSTEAD OF Triggers
            1. Executing INSTEAD OF Triggers
            2. Using AFTER Versus INSTEAD OF Triggers
            3. Using AFTER and INSTEAD OF Triggers Together
            4. Using Views with INSTEAD OF Triggers
            5. INSTEAD OF Trigger Restrictions
        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. 26. 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
            1. Savepoints
            2. Nested Transactions
          3. Implicit Transactions
          4. Implicit Transactions Versus Explicit Transactions
        5. Transaction Logging and the Recovery Process
          1. The Checkpoint Process
            1. Automatic Checkpoints
            2. Manual Checkpoints
          2. The Recovery Process
          3. Managing the Transaction Log
            1. Shrinking the Log File
        6. Transactions and Batches
        7. Transactions and Stored Procedures
        8. Transactions and Triggers
          1. Triggers and Transaction Nesting
          2. Triggers and Multistatement Transactions
          3. Using Savepoints in Triggers
        9. Transactions and Locking
          1. READ_COMMITTED_SNAPSHOT Isolation
        10. Coding Effective Transactions
        11. Long-Running Transactions
        12. Bound Connections
          1. Creating Bound Connections
          2. Binding Multiple Applications
        13. Distributed Transactions
        14. Summary
      10. 27. 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
            1. Providing a Testing (or Quality Assurance) Starting Point (Baseline)
          3. Providing a Point-in-Time Reporting Database
          4. 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. 28. Database Maintenance
        1. What’s New in Database Maintenance
        2. 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
        3. Managing Maintenance Plans Without the Wizard
        4. Executing a Maintenance Plan
        5. Maintenance Without a Maintenance Plan
        6. Summary
    10. V. SQL Server Performance and Optimization
      1. 29. Indexes and Performance
        1. What’s New for Indexes and Performance
        2. Understanding Index Structures
          1. Clustered Indexes
          2. Nonclustered Indexes
        3. Index Utilization
        4. Index Selection
        5. Evaluating Index Usefulness
        6. Index Statistics
          1. The Statistics Histogram
          2. How the Statistics Histogram Is Used
          3. Index Densities
          4. Estimating Rows Using Index Statistics
          5. Generating and Maintaining Index and Column Statistics
            1. Auto-Update Statistics
            2. Asynchronous Statistics Updating
            3. Manually Updating Statistics
            4. Column-Level Statistics
            5. Creating Statistics
            6. String Summary Statistics
        7. SQL Server Index Maintenance
          1. Setting the Fill Factor
          2. Reapplying the Fill Factor
          3. Disabling Indexes
          4. Managing Indexes with SSMS
        8. Index Design Guidelines
          1. Clustered Index Indications
          2. Nonclustered Index Indications
          3. Index Covering
          4. Included Columns
          5. Wide Indexes Versus Multiple Indexes
        9. Indexed Views
        10. Indexes on Computed Columns
        11. Choosing Indexes: Query Versus Update Performance
        12. Summary
      2. 30. Understanding Query Optimization
        1. What’s New in Query Optimization
        2. What Is the Query Optimizer?
        3. Query Compilation and Optimization
          1. Compiling DML Statements
          2. Optimization Steps
        4. Query Analysis
          1. Identifying Search Arguments
          2. Identifying OR Clauses
          3. Identifying Join Clauses
        5. Row Estimation and Index Selection
          1. Evaluating SARG and Join Selectivity
            1. SARGs and Inequality Operators
            2. SARGs and LIKE Clauses
            3. SARGS on Computed Columns
          2. Estimating Access Path Cost
            1. Clustered Index Cost
            2. Nonclustered Index Cost
            3. Covering Nonclustered Index Cost
            4. Table Scan Cost
          3. Using Multiple Indexes
            1. Index Intersection
            2. The Index Union Strategy
            3. Index Joins
          4. Optimizing with Indexed Views
        6. Join Selection
          1. Join Processing Strategies
            1. Nested Loops Joins
            2. Merge Joins
            3. Hash Joins
              1. Grace Hash Joins
          2. Determining the Optimal Join Order
          3. Subquery Processing
            1. IN, ANY, and EXISTS Subqueries
            2. Materialized Subqueries
            3. Correlated Subqueries
        7. Execution Plan Selection
        8. Query Plan Caching
          1. Query Plan Reuse
            1. Simple Query Parameterization
          2. Query Plan Aging
          3. Recompiling Query Plans
            1. Forcing Query Plan Recompiles
          4. Monitoring the Plan Cache
            1. sys.dm_exec_cached_plans
            2. sys.dm_exec_sql_text
            3. sys.dm_exec_query_stats
            4. sys.dm_exec_plan_attributes
        9. Other Query Processing Strategies
          1. Predicate Transitivity
          2. GROUP BY Optimization
          3. Queries with DISTINCT
          4. Queries with UNION
        10. Parallel Query Processing
          1. Parallel Query Configuration Options
          2. Identifying Parallel Queries
        11. Common Query Optimization Problems
          1. Out-of-Date or Unavailable Statistics
          2. Poor Index Design
          3. Search Argument Problems
            1. Using Optimizable SARGs
            2. No SARGs
            3. Unknown Values in WHERE Clauses
            4. Data Type Mismatches
          4. Large Complex Queries
          5. Triggers
        12. Managing the Optimizer
          1. Optimizer Hints
            1. Forcing Index Selection with Table Hints
            2. Forcing Join Strategies with Join Hints
            3. Specifying Query Processing Hints
            4. GROUP BY Hints
            5. UNION Hints
            6. Join Hints
            7. Miscellaneous Hints
          2. Using the USE PLAN Query Hint
          3. Using Plan Guides
            1. Creating Plan Guides
            2. Managing Plan Guides
            3. The sys.plan_guides Catalog View
            4. Plan Guide Best Practices
            5. Verifying That a Plan Guide Is Being Applied
          4. Forced Parameterizaion
            1. Guidelines for Using Forced Parameterization
          5. Limiting Query Plan Execution with the Query Governor
        13. Summary
      3. 31. Query Analysis
        1. What’s New in Query Analysis
        2. Query Analysis in SSMS
          1. Execution Plan ToolTips
          2. Logical and Physical Operator Icons
            1. Assert
            2. Clustered Index Delete, Insert, and Update
            3. Nonclustered Index Delete, Insert, and Update
            4. Clustered Index Seek and Scan
            5. Nonclustered Index Scan and Seek
            6. Split and Collapse
            7. Compute Scalar
            8. Concatenation
            9. Constant Scan
            10. Deleted Scan and Inserted Scan
            11. Filter
            12. Hash Match
            13. Nonclustered Index Spool, Row Count Spool, and Table Spool
            14. Eager Spool or Lazy Spool
            15. Log Row Scan
            16. Merge Join
            17. Nested Loops
            18. Parameter Table Scan
            19. Remote Delete, Remote Insert, Remote Query, Remote Scan, and Remote Update
            20. RID Lookup
            21. Sequence
            22. Sort
            23. Stream Aggregate
            24. Table Delete, Table Insert, Table Scan, and Table Update
            25. Table-valued Function
            26. Top
            27. Parallelism Operators
            28. Distribute Streams
            29. Gather Streams
            30. Repartition Streams
          3. Analyzing Stored Procedures
          4. Saving and Viewing Graphical Execution Plans
        3. SSMS Client Statistics
        4. Using the SET SHOWPLAN Options
          1. SHOWPLAN_TEXT
          2. SHOWPLAN_ALL
          3. SHOWPLAN_XML
        5. Using sys.dm_exec_query_plan
        6. Query Statistics
          1. STATISTICS IO
            1. Scan Count
            2. Logical Reads
            3. Physical Reads
            4. Read-Ahead Reads
            5. LOB Reads
            6. Analyzing STATISTICS IO Output
          2. STATISTICS TIME
          3. Using datediff() to Measure Runtime
          4. STATISTICS PROFILE
          5. STATISTICS XML
        7. Query Analysis with SQL Server Profiler
        8. Summary
      4. 32. 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 SSMS
          3. Viewing Locking Activity with SQL Server Profiler
          4. 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
            1. Key-Range Locking for a Range Search
            2. Key-Range Locking When Searching Nonexistent Rows
          2. Using Application Locks
          3. Index Locking
          4. Row-Level Versus Page-Level Locking
          5. Lock Escalation
          6. The locks Configuration Setting
        8. Lock Compatibility
        9. Locking Contention and Deadlocks
          1. Identifying Locking Contention
          2. Setting the Lock Timeout Interval
          3. Minimizing Locking Contention
          4. Deadlocks
            1. Avoiding Deadlocks
            2. Handling and Examining Deadlocks
              1. The 1204 Trace Flag
              2. The 1222 Trace Flag
            3. Monitoring Deadlocks with SQL Server Profiler
        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 timestamp Data Type
          2. Optimistic Locking with Snapshot Isolation
        12. Summary
      5. 33. Database Design and Performance
        1. What’s New in Database Design and Performance
        2. Basic Tenets of Designing for Performance
        3. Logical Database Design Issues
          1. Normalization Conditions
          2. Normalization Forms
            1. First Normal Form
            2. Second Normal Form
            3. Third Normal Form
          3. Benefits of Normalization
          4. Drawbacks of Normalization
        4. Denormalizing a Database
          1. Denormalization Guidelines
          2. Essential Denormalization Techniques
            1. Redundant Data
            2. Computed Columns
            3. Summary Data
            4. Horizontal Data Partitioning
            5. Vertical Data Partitioning
            6. Performance Implications of Zero-to-One Relationships
        5. Database Filegroups and Performance
        6. RAID Technology
          1. RAID Level 0
          2. RAID Level 1
          3. RAID Level 10
          4. RAID Level 5
        7. Summary
      6. 34. Monitoring SQL Server Performance
        1. What’s New in Monitoring SQL Server Performance
        2. A Performance Monitoring Approach
        3. Performance Monitor
          1. Performance Monitor Views
          2. Monitoring Values
        4. Windows Performance Counters
          1. Monitoring the Network Interface
            1. Dynamic Management Views or System Views for Monitoring Network Items
          2. Monitoring the Processors
            1. Dynamic Management Views or System Views for Monitoring Processor Items
          3. Monitoring Memory
            1. Dynamic Management Views or System Views for Monitoring Memory Items
          4. Monitoring the Disk System
            1. Dynamic Management Views or System Views for Monitoring Disk System Items
        5. SQL Server Performance Counters
          1. MSSQL$:Plan Cache Object
          2. Monitoring SQL Server’s Disk Activity
          3. Locks
          4. Users
          5. The Procedure Cache
          6. User-Defined Counters
            1. Dynamic Management Views or System Views: Access to Perfmon Counters
        6. Using DBCC to Examine Performance
          1. SQLPERF
          2. PERFMON
          3. SHOWCONTIG
          4. PROCCACHE
          5. INPUTBUFFER and OUTPUTBUFFER
        7. The Top 100 Worst-Performing Queries
        8. Other SQL Server Performance Considerations
        9. Summary
    11. VI. SQL Server Application Development
      1. 35. What’s New for Transact-SQL in SQL Server 2005
        1. The xml Data Type
        2. The max Specifier
        3. TOP Enhancements
        4. The OUTPUT Clause
        5. Common Table Expressions
          1. Recursive Queries with CTEs
            1. Using Recursive CTEs for Expanding a Hierarchy
            2. Setting the MAXRECURSION Option
        6. Ranking Functions
          1. The ROW_NUMBER Function
            1. Partitioning by ROW_NUMBER()
          2. The RANK and DENSE_RANK Functions
          3. The NTILE Function
          4. Using Row Numbers for Paging Results
        7. PIVOT and UNPIVOT
        8. The APPLY Operator
          1. CROSS APPLY
          2. OUTER APPLY
        9. TRY...CATCH Logic for Error Handling
        10. The TABLESAMPLE Clause
        11. Summary
      2. 36. SQL Server and the .NET Framework
        1. What’s New in SQL Server 2005 and the .NET Framework
        2. Working with ADO.NET 2.0 and SQL Server
          1. ADO.NET: Advanced Basics
          2. What’s New in ADO.NET for SQL Server 2005
            1. Multiple Active Result Sets (MARS)
            2. Additions to System.Data.SqlTypes
            3. Connection Pooling
            4. Query Notification
        3. Developing Custom Managed Database Objects
          1. An Introduction to Custom Managed Database Objects
          2. Managed Object Permissions
            1. The Three Permission Sets
          3. Developing Managed Objects with Visual Studio 2005
          4. Using Managed Stored Procedures
            1. Attributes and the Implementation Contract
            2. The Context Connection
            3. Objects in Microsoft.SqlServer.Server
            4. Setting Up the Server for Managed Code Execution
            5. Building and Deploying the Assembly
            6. Debugging Managed Code
          5. Using Managed User-Defined Functions (UDFs)
            1. Scalar UDFs
            2. Table-Valued UDFs (TVFs)
          6. Using Managed User-Defined Types (UDTs)
          7. Using Managed User-Defined Aggregates (UDAs)
          8. Using Managed Triggers
          9. Using Transactions
          10. Using the Related System Catalogs
        4. Summary
      3. 37. Using XML in SQL Server 2005
        1. What’s New in Using XML in SQL Server 2005
        2. Understanding XML
        3. Relational Data as XML: The FOR XML Modes
          1. RAW Mode
            1. Working with Binary Columns
          2. AUTO Mode
          3. EXPLICIT Mode
          4. PATH Mode
          5. FOR XML and the New xml Data Type
        4. XML as Relational Data: Using OPENXML
        5. Using the New xml Data Type
          1. Defining and Using xml Columns
          2. Using XML Schema Collections
          3. The Built-in xml Data Type Methods
            1. Selecting XML by Using query()
              1. The for Clause
              2. The where Clause
              3. The order by Clause
              4. The return Clause
            2. Testing XML by Using exist()
            3. Converting a Node Value to a T-SQL Data Type by Using value()
              1. Accessing Relational Columns and T-SQL Variables in XQuery Expressions
            4. Using the nodes() Method to Shred XML
            5. Using modify() to Insert, Update, and Delete XML
              1. Removing XML Nodes by Using delete
              2. Modifying XML with insert and replace value of
        6. Indexing and Full-Text Indexing of xml Columns
          1. Indexing xml Columns
            1. Understanding XML Indexes
              1. The Primary XML Index
              2. The Secondary XML Indexes
                1. The PATH Secondary XML Index
                2. The VALUE Secondary XML Index
                3. The PROPERTY Secondary XML Index
            2. XML Index Performance Considerations
          2. Full-Text Indexing
        7. Summary
      4. 38. SQL Server Web Services
        1. What’s New in SQL Server Web Services
        2. Web Services History and Overview
          1. The Web Services Pattern
        3. Building Web Services
          1. The AS HTTP Keyword Group
          2. The FOR SOAP Keyword Group
        4. 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
        5. Using Catalog Views and System Stored Procedures
        6. Controlling Access Permissions
        7. Summary
    12. VII. SQL Server Business Intelligence Features
      1. 39. SQL Server 2005 Analysis Services
        1. What’s New in SSAS
        2. Understanding SSAS and OLAP
        3. Understanding the SSAS Environment Wizards
          1. OLAP Versus OLTP
            1. MOLAP
            2. ROLAP
            3. HOLAP
        4. An Analytics Design Methodology
          1. An Analytics Mini-Methodology
            1. Requirements Phase
            2. Design Phase
            3. Construction Phase
            4. Implementation Phase
            5. Maintenance Phase
        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
            1. Adding a Data Source
            2. Creating Data Source Views
            3. Defining Dimensions and Hierarchies
            4. Creating the Cube
            5. Building and Deploying the Cube
            6. Populating the Cube with Data
            7. Aggregating Data Within the Cube
            8. Browsing Data in the Cube
            9. Delivering Data to Users
            10. Multidimensional Expressions
            11. ADO MD
            12. Calculated Members (Calculations)
            13. Query Analysis and Optimization
          5. Generating a Relational Database
            1. Limitations of a Relational Database
          6. Cube Perspectives
          7. KPIs
          8. Data Mining
            1. SSIS
            2. OLAP Performance
          9. Security and Roles
        6. Summary
      2. 40. 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. Using bcp
          1. Fundamentals of Exporting and Importing Data
          2. File Data Types
          3. Format Files
            1. Customizing a Format File by Using Interactive bcp
            2. Creating a Format File by Using the format Switch
            3. File Storage Types
            4. Prefix Lengths
            5. Field Lengths
            6. Field Terminators
            7. Different Numbers of Columns in a File and a Table
            8. Renumbering Columns
          4. Using Views
        10. Logged and Non-Logged Operations
          1. Batches
          2. Parallel Loading
          3. Supplying Hints to bcp
            1. The ROWS_PER_BATCH Hint
            2. The CHECK_CONSTRAINTS Hint
            3. The FIRE_TRIGGER Hint
            4. The ORDER Hint
            5. The KILOBYTES_PER_BATCH Hint
            6. The TABLOCK Hint
        11. Summary
      3. 41. SQL Server 2005 Reporting Services
        1. What’s New in Reporting Services 2005
          1. Report Builder
          2. The Report Viewer Controls
        2. Installing and Configuring Reporting Services
          1. The Reporting Services System Architecture
          2. Installing Reporting Services
            1. The Default Installation
            2. Custom (Files-Only) Installation
          3. Reporting Services Configuration Options and Tools
            1. The Reporting Services Configuration Tool
            2. Report Server Encryption
            3. Configuring the Report Server Windows Service
            4. Configuring the Report Server Web Service
            5. Creating and Configuring the Report Server Database Catalog
            6. The Surface Area Configuration Tool
        3. Designing Reports
          1. Designing Reports by Using the BIDS Report Designer
            1. Designing Queries
            2. Making Layout Choices
            3. Building Reports
            4. Creating a Simple List
            5. Grouping and Sorting in a Report
            6. Using Tables and Hierarchies
            7. Adding Interactivity
            8. Deploying the Sample Report
          2. Designing Reports Using Report Builder
          3. Models and the Model Designer
          4. A Model Design Example
            1. Defining Inheritance
            2. Promoting Properties
            3. The Report Builder Design Surface
            4. Discourage Grouping
            5. Roles and Drill-Through Reports
            6. Debugging Models and Model Queries
            7. Sorting in Report Builder
            8. Generating Drill-Through Reports
            9. Saving and Opening Reports
            10. Customizing Drill-Through Reports
            11. Other Model and Model Designer Features
          5. Model Security
          6. Enabling Ad Hoc Reporting
            1. Granting Execute Report Definitions—A Global Permission
            2. Setting Permissions on the Model
        4. Management and Security
          1. Deploying Reports
          2. Scripting Support in Reporting Services
          3. Securing Reports
            1. Built-in Roles and Permissions
            2. System Roles and System Permissions
            3. Authentication of Report Server Users: Windows and Forms
          4. Subscriptions
            1. Data-Driven Subscriptions
            2. Subscription and Delivery Architecture
          5. Report Execution Options
            1. Live Reports and Sessions
            2. Cached Reports
            3. Execution Snapshots
            4. History Snapshots
            5. Limitations for Cached Reports and Execution Snapshots and History Snapshots
        5. Performance and Monitoring Tools
          1. The Server Trace Log
          2. The Execution Log
          3. Event Log Entries
          4. Performance Counters
        6. Building Applications for SQL Server Reporting Services 2005 Using the Report Viewer Controls
          1. Using the ASP.NET Report Controls in a Website
        7. Summary
    13. VIII. Bonus Chapters on the CD
      1. 42. 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 Through SQL Server Management Studio
        9. Summary
      2. 43. 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. Ad Hoc Distributed Queries
          2. affinity I/O mask
          3. affinity mask
          4. AWE Enabled
          5. CLR Enabled
          6. Cost Threshold for Parallelism
          7. Cursor Threshold
          8. Default Full-Text Language
          9. Default Language
          10. Fill Factor
          11. Index Create Memory (KB)
          12. Lightweight Pooling
          13. Locks
          14. Max Degree of Parallelism
          15. Max Server Memory and Min Server Memory
          16. Max Text Repl Size
          17. Max Worker Threads
          18. Min Memory Per Query
          19. Nested Triggers
          20. Network Packet Size
          21. Priority Boost
          22. Query Governor Cost Limit
          23. Query Wait
          24. Recovery Interval
          25. Remote Admin connections
          26. Remote Login timeout
          27. Remote Proc Trans
          28. Remote Query timeout
          29. Scan for Startup Procs
          30. Show Advanced Options
          31. User Connections
          32. User Options
          33. XP-Related Configuration Options
          34. Miscellaneous Options
        8. Database Engine Tuning Advisor
          1. The Database Engine Tuning Advisor GUI
          2. The Database Engine Tuning Advisor Command Line
        9. Summary
      3. 44. Administering Very Large SQL Server Databases
        1. What’s New for Administering Very Large SQL Server Databases
        2. Do I Have a VLDB?
        3. VLDB Maintenance Issues
          1. Backing Up and Restoring VLDBs
            1. Snapshot Backups
          2. Checking VLDB Consistency
            1. Developing a Consistency Checking Plan
          3. Data Maintenance
            1. Updating Statistics
            2. Rebuilding Indexes
            3. Purging/Archiving Data
              1. Locking and Performance Considerations
              2. Logging Considerations
              3. Referential Integrity Considerations
              4. Transactional Integrity Considerations
              5. Storage/Retrieval of Archived Data
              6. Possible Archive Solutions
              7. Data Archival and Database Design Changes
              8. Logical Purging/Archiving
        4. VLDB Database Design Considerations
          1. Database Partitioning Options and Issues
            1. Vertical Data Partitioning
            2. Horizontal Data Partitioning
            3. Using Distributed Partitioned Views
            4. Partitioning Databases Across Servers
            5. Table and Index Partitioning for VLDBs
        5. Summary
      4. 45. 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
            1. Active/Passive DR Sites Pattern
            2. Active/Active DR Sites Pattern
            3. Active Multisite DR Pattern
            4. Choosing a Disaster Recovery Pattern
          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
      5. 46. Transact-SQL Programming Guidelines, Tips, and Tricks
        1. General T-SQL Coding Recommendations
          1. Provide Explicit Column Lists
          2. Qualify Object Names with Schema Name
          3. Avoiding SQL Injection Attacks When Using Dynamic SQL
          4. Comment Your T-SQL Code
        2. 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. Using Temp Tables Versus Table Variables Versus Common Table Expressions
          5. Avoid Unnecessary Function Executions
          6. Cursors and Performance
            1. When to Use Cursors
          7. Variable Assignment in UPDATE Statements
        3. T-SQL Tips and Tricks
          1. Date Calculations
            1. Calculating the First Day of Month
            2. Calculating the First Day of the Year
            3. Calculating the First Day of the Quarter
            4. Calculating Midnight for the Current Day
            5. Calculating Monday of the Current Week
            6. Calculating Other Dates
            7. Converting Dates for Comparison
          2. Sorting Results with the GROUPING Function
          3. Using CONTEXT_INFO
          4. Working with Outer Joins
            1. OUTER JOIN Versus WHERE Clause Matching
            2. Nested Outer Joins
            3. Working with Full Outer Joins
          5. Generating T-SQL Statements with T-SQL
          6. Working with @@ERROR and @@ROWCOUNT
          7. De-Duping Data with Ranking Functions
        4. Summary
      6. 47. SQL Server Notification Services
        1. What’s New in SQL Server Notification Services
        2. Requirements and Editions of SSNS
        3. Making the Business Case for Using SSNS
        4. Understanding the SSNS Platform Architecture
          1. Understanding Events
          2. Understanding Event Providers
          3. Understanding Subscribers and Subscriptions
          4. Understanding Event Rules
            1. Understanding Actions
          5. Understanding the Notification Cycle
          6. Understanding Instances
        5. Building an Effective SSNS Application
          1. Choosing a Programming Method
            1. Using XML
            2. Using NMO
          2. Working with XML Using Management Studio
          3. Learning the Essentials of ADFs
            1. Use Case for the Sample Application
            2. Using the ParameterDefaults ADF Element
            3. Using the Database ADF Element
            4. Using the EventClasses ADF Element
            5. Using the SubscriptionClasses ADF Element
            6. Using the NotificationClasses ADF Element
              1. Formatting Notifications
            7. Using the Providers ADF Element
            8. Using the Generator ADF Element
            9. Using the Distributors ADF Element
          4. Learning the Essentials of ICFs
            1. Using the ParameterDefaults, InstanceName, SqlServerSystem, and Applications ICF Elements
            2. Using the Database ICF Element
            3. Using the DeliveryChannels ICF element
        6. Compiling and Running the Sample Application
          1. Creating the Instance and Application via SSMS
          2. Creating Subscriptions
          3. Providing Events to the Application
        7. Summary
      7. 48. SQL Server Service Broker
        1. What’s New in Service Broker
        2. Understanding Distributed Messaging
          1. The Basics of Service Broker
        3. Designing an Example 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
            1. Creating the Conversation Initiator
            2. Creating the Conversation Target
        5. Service Broker Routing and Security
          1. Using Certificates for Conversation Encryption
            1. Building Routes to Map Conversations Between SQL Server Instances
            2. Creating Remote Service Bindings for Conversations
          2. A Final Note on the Example System
        6. Related System Catalogs
        7. Summary
      8. 49. SQL Server Full-Text Search
        1. What’s New in SQL Server 2005 Full-Text Search
        2. How SQL Server FTS Works
        3. Setting Up a Full-Text Index
          1. Using T-SQL Commands to Build Full-Text Indexes and Catalogs
            1. CREATE FULLTEXT CATALOG
              1. ON FILEGROUP
              2. IN PATH
              3. WITH ACCENT_SENSITIVITY
              4. AS DEFAULT
              5. AUTHORIZATION
            2. CREATE FULLTEXT INDEX
              1. COLUMN NAME
              2. TYPE COLUMN
              3. Full-Text Indexing of Blobs and XML
              4. LANGUAGE
              5. ON FULLTEXT CATALOG
              6. KEY INDEX
              7. POPULATION TYPE
            3. ALTER FULLTEXT INDEX
              1. ENABLE and DISABLE
              2. SET CHANGE_TRACKING
              3. ADD
              4. DROP
              5. START and STOP
            4. Managing MSFTESQL
            5. Diagnostics
          2. Using the Full-Text Indexing Wizard to Build Full-Text Indexes and Catalogs
        4. Full-Text Searches
          1. CONTAINS and CONTAINSTABLE
            1. Search Phrase
            2. Generation
            3. Proximity
            4. Weighted
            5. LANGUAGE
            6. CONTAINSTABLE
          2. FREETEXT and FREETEXTTABLE
          3. Noise Words
        5. Full-Text Search Maintenance
          1. Backup and Restore of Full-Text Catalogs
          2. Attachment and Detachment of Full-Text Catalogs
        6. Full-Text Search Performance
        7. Summary

    Product information

    • Title: Microsoft® SQL Server 2005 Unleashed
    • Author(s):
    • Release date: April 2007
    • Publisher(s): Sams
    • ISBN: 0672328240