Microsoft® SQL Server® 2008 Step by Step

Microsoft® SQL Server® 2008 Step by Step

by Mike Hotek
Released November 2008
Publisher(s): Microsoft Press
ISBN: 9780735626041

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

Teach yourself SQL Server 2008—one step at a time. Get the practical guidance you need to build database solutions that solve real-world business problems. Learn to integrate SQL Server data in your applications, write queries, develop reports, and employ powerful business intelligence systems.

Discover how to:

  • Install and work with core components and tools

  • Create tables and index structures

  • Manipulate and retrieve data

  • Secure, manage, back up, and recover databases

  • Apply tuning plus optimization techniques to generate high-performing database applications

  • Optimize availability through clustering, database mirroring, and log shipping

  • Tap business intelligence tools—Reporting, Analysis, and Integration Services



    • CD features:

  • Practice exercises and code samples

  • Fully searchable eBook



    • For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

    Table of contents

    1. Acknowledgments
    2. Introduction
      1. Who This Book Is For
      2. How This Book Is Organized
      3. Finding Your Best Starting Point in This Book
      4. Conventions and Features in This Book
        1. Conventions
        2. Other Features
      5. System Requirements
      6. Sample Databases
      7. Practice Files
        1. Installing the Practice Files
        2. Using the Practice Files
        3. Uninstalling the Practice Files
      8. Find Additional Content Online
      9. Support for This Book
        1. Questions and Comments
    3. I. Getting Started with Microsoft SQL Server 2008
      1. 1. Overview of Microsoft SQL Server
        1. Database Engine
          1. Storage Engine
          2. Security Subsystem
          3. Programming Interfaces
          4. Service Broker
          5. SQL Server Agent
          6. Replication
          7. High Availability
          8. The Relational Engine in SQL Server 2008
        2. Business Intelligence
          1. Integration Services
            1. Integration Services in SQL Server 2008
          2. Reporting Services
            1. Reporting Services in SQL Server 2008
          3. Analysis Services
            1. Analysis Services in SQL Server 2008
        3. Chapter 1 Quick Reference
      2. 2. Installing and Configuring SQL Server 2008
        1. Editions of SQL Server 2008
        2. Infrastructure Requirements
        3. Service Accounts
        4. Collation Sequences
        5. Authentication Modes
        6. SQL Server Instances
        7. Upgrading to SQL Server 2008
          1. In-Place Upgrade
          2. Side-by-Side Upgrade
            1. Upgrade Methods
        8. Installing SQL Server 2008
          1. Install Sample Databases
        9. Chapter 2 Quick Reference
      3. 3. Using the Tools in SQL Server 2008
        1. SQL Server Documentation
        2. Management Tools in SQL Server 2008
          1. SQL Server Configuration Manager
          2. SQL Server Management Studio
          3. Database Mail
        3. Performance Management Tools
          1. Profiler
          2. Database Engine Tuning Advisor
          3. Performance Studio
        4. Business Intelligence Tools
          1. Business Intelligence Development Studio
        5. Chapter 3 Quick Reference
    4. II. Designing Databases
      1. 4. Creating Databases
        1. SQL Server System Databases
        2. SQL Server Database Structure
          1. Database Files
          2. Filegroups
        3. Creating a Database
        4. Moving Databases
          1. Detaching a Database
          2. Attaching a Database
        5. Chapter 4 Quick Reference
      2. 5. Designing Tables
        1. Naming Objects
        2. Schemas
        3. Data Types
          1. Numeric Data
            1. Exact and Approximate Numeric Data Types
            2. Decimal Data Types
          2. Character Data
            1. Fixed and Variable Length Character Data
            2. Unicode Data
            3. Varchar(max) and nvarchar(max)
          3. Date and Time Data
          4. Binary Data
          5. XML
          6. FILESTREAM Data
          7. Spatial Data Type
          8. HierarchyID Data Type
        4. Column Properties
        5. Creating Tables
        6. Computed Columns
        7. Sparse Columns
        8. Constraints
          1. Primary Keys
          2. Unique Constraints
          3. Check Constraints
          4. Default Constraints
          5. Foreign Keys
        9. Database Diagrams
        10. Chapter 5 Quick Reference
      3. 6. Indexes
        1. Index Structure
          1. Balanced Trees (B-trees)
          2. Index levels
        2. Clustered Indexes
        3. Nonclustered Indexes
          1. Index Maintenance
        4. Included Columns
          1. Covering Indexes
        5. Filtered Indexes
        6. Online Index Creation
        7. Index Management and Maintenance
          1. Index Fragmentation
          2. Fill Factor
          3. Defragmenting an Index
          4. Disabling an index
        8. XML Indexes
        9. Spatial Indexes
        10. Chapter 6 Quick Reference
      4. 7. Partitioning
        1. Partition Functions
        2. Partition Schemes
          1. Filegroups
        3. Partitioning Tables and Indexes
          1. Partial Backup and Restore
          2. Creating a Partitioned Index
        4. Managing Partitions
          1. SPLIT and MERGE Operators
          2. Altering a Partition Scheme
          3. Index Alignment
          4. SWITCH Operator
        5. Chapter 7 Quick Reference
    5. III. Retrieving and Manipulating Data
      1. 8. Data Retrieval
        1. General SELECT Statement
        2. Sorting Results
        3. Filtering Data
        4. Retrieving from More Than One Table
        5. Retrieving Unique Results
        6. Chapter 8 Quick Reference
      2. 9. Advanced Data Retrieval
        1. General SELECT Statement
        2. Aggregating Data
          1. Aggregating Multiple Permutations
          2. Filtering Aggregates
          3. Running Aggregates
          4. Calculating Pivot Tables
          5. Ranking Data
        3. Aggregating Result Sets
        4. Common Table Expressions
        5. Querying XML Data
        6. Chapter 9 Quick Reference
      3. 10. Data Manipulation
        1. Inserting Data
          1. INSERT
          2. SELECT INTO
        2. Updating Data
        3. Deleting Data
          1. TRUNCATE
        4. MERGE Statement
        5. OUTPUT Clause
        6. Transaction Handling
        7. Tracking Changes
          1. Change Tracking
          2. Change Data Capture
        8. Chapter 10 Quick Reference
    6. IV. Designing Advanced Database Objects
      1. 11. Views
        1. Creating a View
          1. Query Substitution
        2. Modifying Data Through a View
        3. Creating an Indexed View
          1. Query Substitution
        4. Chapter 11 Quick Reference
      2. 12. Stored Procedures
        1. Creating Stored Procedures
        2. Commenting Code
        3. Variables, Parameters, and Return Codes
          1. Variables
          2. Parameters
          3. Return Codes
        4. Executing Stored Procedures
        5. Control Flow Constructs
        6. Error Handling
        7. Dynamic Execution
        8. Cursors
        9. CLR Procedures
        10. Building an Administrative Procedure
        11. Chapter 12 Quick Reference
      3. 13. Functions
        1. System Functions
        2. Creating a Function
        3. Retrieving Data from a Function
        4. Chapter 13 Quick Reference
      4. 14. Triggers
        1. DML Triggers
        2. DDL Triggers
        3. Chapter 14 Quick Reference
      5. 15. Database Snapshots
        1. Creating a Database Snapshot
          1. Copy-On-Write Technology
        2. Reverting Data Using a Database Snapshot
        3. Chapter 15 Quick Reference
      6. 16. Service Broker
        1. Service Broker Architecture
          1. Messaging Overview
          2. Service Broker Components
          3. Application Interaction
        2. Message Types and Contracts
          1. Message Types
          2. Contracts
        3. Queues and Services
          1. Queues
          2. Services
        4. Conversations
        5. Sending and Receiving Messages
          1. Sending Messages
          2. Receiving Messages
        6. Queue Activation
        7. Prioritization
        8. Chapter 16 Quick Reference
      7. 17. Full-Text Indexing
        1. Full-Text Catalogs
        2. Full-Text Indexes
        3. Querying Full-Text Data
          1. FREETEXT
          2. CONTAINS
        4. Chapter 17 Quick Reference
    7. V. Database Management
      1. 18. Security
        1. Configuring the Attack Surface
        2. Endpoints
          1. Endpoint Types and Payloads
          2. Endpoint Access
          3. TCP Endpoints
            1. TCP Protocol Arguments
            2. Database Mirroring Arguments
            3. Service Broker Arguments
            4. Encryption
        3. Principals, Securables, and Permissions
          1. Principals
            1. Instance Level Principals
            2. Database Level Principals
          2. Impersonation
          3. Securables
          4. Permissions
          5. Ownership Chains
            1. Metadata Security
        4. CLR Security
        5. Data Encryption
          1. Master Keys
            1. Service Master Key
            2. Database Master Key
          2. Hash Algorithms
          3. Symmetric Keys
          4. Certificates and Asymmetric Keys
            1. Asymmetric Keys
            2. Certificates
          5. Transparent Data Encryption
          6. Encryption Key Management
        6. Chapter 18 Quick Reference
      2. 19. Policy-Based Management
        1. Overview of Policy-Based Management
        2. Facets
        3. Conditions
        4. Policy Targets
        5. Policies
        6. Policy Categories
        7. Policy Compliance
        8. Chapter 19 Quick Reference
      3. 20. Data Recovery
        1. Database Backups
          1. Backup Types
            1. Full Backups
            2. Transaction Log Backups
            3. Differential Backups
            4. Filegroup Backups
          2. Page Corruption
        2. Recovery Models
        3. Database Restores
          1. Restoring a Full Backup
            1. Restore Paths
          2. Restoring a Differential Backup
          3. Restoring a Transaction Log Backup
        4. Chapter 20 Quick Reference
      4. 21. SQL Server Agent
        1. Creating Jobs
          1. Jobs Steps
          2. Job Schedules
          3. Operators
        2. Creating Maintenance Plans
        3. Creating Alerts
        4. Chapter 21 Quick Reference
      5. 22. Dynamic Management Views
        1. Overview of DMVs
        2. Retrieving Object Metadata
        3. Database Diagnostics
          1. Object Size
          2. Indexes
          3. Query Execution Statistics
        4. Chapter 22 Quick Reference
    8. VI. High Availability Overview
      1. 23. High Availability
        1. Failover Clustering
          1. Failover Cluster Instance Components
          2. Network Configuration
          3. Disk Configuration
          4. Security Configuration
          5. Health Checks
          6. Cluster Failover
        2. Database Mirroring
          1. Database Mirroring Roles
            1. Principal Role
            2. Mirror Role
            3. Witness Server
            4. Database-level vs. Server-level Roles
          2. Database Mirroring Endpoints
          3. Operating Modes
            1. High Availability Operating Mode
            2. High Performance Operating Mode
            3. High Safety Operating Mode
          4. Caching
          5. Transparent Client Redirect
          6. Corrupt Pages
          7. Database Snapshots
          8. Initializing Database Mirroring
            1. Recovery Model
            2. Backup and Restore
        3. Log Shipping
          1. Log Shipping Components
            1. Primary Database
            2. Secondary Database
            3. Monitor Server
          2. Log Shipping Initialization
            1. Creating Jobs
            2. Data Loss Exposure
            3. Restoring Backups
            4. Restoring a Full Backup During Configuration
        4. Replication
          1. Replication Components
            1. Articles
            2. Publications
            3. Filters
          2. Replication Roles
          3. Replication Agents
            1. Snapshot Agent
            2. Log Reader Agent
            3. Distribution Agent
            4. Merge Agent
            5. Queue Reader Agent
          4. Replication Methods
            1. Snapshot Replication
            2. Transactional Replication
            3. Merge Replication
        5. Chapter 23 Quick Reference
    9. VII. Business Intelligence
      1. 24. SQL Server Integration Services
        1. BIDS Overview
        2. Tasks
        3. Transforms
        4. Building a Package
          1. Connections
          2. Control Flow
          3. Data Flow
          4. Data Conversion
          5. Exception Handling
          6. Configuration
          7. Deployment
        5. Chapter 24 Quick Reference
      2. 25. SQL Server Reporting Services
        1. Configuring Reporting Services
        2. Reporting Services Web Site
        3. Creating Reports
          1. Building a Report
          2. Formatting
          3. Computations
          4. Interactive Elements
          5. Parameters
        4. Deploy Reports
        5. Report Subscriptions
        6. Linked Reports
        7. Report Caching and Snapshots
        8. Chapter 25 Quick Reference
      3. 26. SQL Server Analysis Services
        1. Data Warehousing Overview
        2. Online Analytic Processing (OLAP)
          1. Dimensional Model
          2. Cubes
          3. Dimensions, Measures, and Calculations
          4. Hierarchies
        3. KPIs, Partitions, Perspectives, and Translations
          1. Key Performance Indicators (KPIs)
          2. Partitions
          3. Perspectives
          4. Translations
        4. Data Mining
          1. Algorithms
            1. Algorithm Categories
            2. SSAS Data Mining Algorithms
          2. Mining Models and Mining Structures
          3. Data Mining Demystified
        5. Chapter 26 Quick Reference
    10. A. About the Author
    11. B. Additional Resources for Developers from Microsoft Press
      1. Visual Basic
      2. Visual C#
      3. Web Development
      4. Data Access
      5. SQL Server
      6. Other Developer Topics
    12. C. More Great Developer Resources
      1. Developer Step by Step
      2. Developer Reference
      3. Focused Topics
    13. Index
    14. About the Author
    15. Copyright

    Product information

    • Title: Microsoft® SQL Server® 2008 Step by Step
    • Author(s): Mike Hotek
    • Release date: November 2008
    • Publisher(s): Microsoft Press
    • ISBN: 9780735626041