You are previewing Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®.
O'Reilly logo
Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

Book Description

If you’re in charge of database administration, developing database software, or looking for database solutions for your company, Microsoft SQL Server 2008 All-In-One Desk Reference For Dummies can help you get a handle on this extremely popular relational database management system. Here you’ll find what’s new in the latest version; how to choose and install the right variation for your needs; how to monitor, maintain, and protect your data; and what it takes to keep your database healthy. You’ll discover how to:

  • Build and maintain tables

  • Design a database and communicate with it

  • Retrieve, analyze, and report data

  • Build solid, robust database applications

  • Use the SQL Server Optimizer and Query Designer

  • Navigate SQL Server with Visual Studio

  • Develop useful reports with the Report Builder and Report Designer

  • Create Business Intelligence solutions with Business Intelligence Development Studio

  • Configure your server and perform major administrative tasks

To help you quickly find what you need, Microsoft SQL Server 2008 All-In-One Desk Reference For Dummies is divided into nine minibooks:

  • Essential Concepts

  • Designing and Using Databases

  • Interacting With Your Data

  • Database Programming

  • Reporting Services

  • Analysis Services

  • Performance Tips and Tricks

  • Database Administration

  • Appendixes

Microsoft SQL Server 2008 All-In-One Desk Reference For Dummies gets you started, helps you solve problems, and will even answer your questions down the road!

Table of Contents

  1. Copyright
  2. About the Authors
  3. Dedication
  4. Authors' Acknowledgments
  5. Introduction
  6. Essential Concepts
    1. Introducing SQL Server 2008
      1. SQL Server 2008: An Evolution, Not a Revolution
      2. Understanding SQL Server's Editions
    2. SQL Server Architecture and Key Concepts
      1. Relational Databases: The Heart of Modern Computing Solutions
      2. Understanding Key SQL Server 2008 Concepts
      3. Administration
      4. Application Development
      5. Business Intelligence
      6. Reporting
      7. Integration
    3. Getting Started, Getting Around
      1. Hardware and Software Requirements
      2. Converting to SQL Server 2008
      3. Tools at Your Disposal
    4. Setting Up SQL Server 2008
      1. Installing SQL Server
      2. Creating and Maintaining Configurations
      3. Streamlining Administration
    5. Using SQL Server Management Studio
      1. Menu Structure and Icons
      2. Object Explorer
      3. Template Explorer
      4. Solution Explorer
      5. Running Queries
  7. Designing and Using Databases
    1. Setting Up a Database
      1. System Databases
      2. Connecting to a Database Server
      3. Exploring an Existing Database
      4. Understanding the Major Database Objects
      5. Creating a New Database
      6. Using SQLCMD to Create a Database
      7. Scripting Your Database
    2. Care and Feeding of Your Database
      1. Renaming a Database
      2. Changing Database Parameters
      3. Deleting a Database
    3. Data Types and How to Use Them
      1. Traditional Data Types
      2. Enhanced Data Types
      3. Creating Your Own Data Types
      4. Assigning a Data Type
    4. Constructing New Tables
      1. Building a New Table
      2. Additional Column Options
      3. Viewing Table Properties
      4. Creating Views
      5. Creating a Table via SQLCMD
    5. Looking After Your Tables
      1. Getting a List of Your Tables
      2. Determining Dependencies
      3. Viewing the Table's Contents
      4. Modifying a Table
      5. Deleting a Table
      6. Altering a Table via SQLCMD
    6. Understanding Relationships
      1. Relationships: Making Data Meaningful
      2. Relationship Types
      3. Creating Relationships
      4. Managing Relationship Errors
  8. Interacting with Your Data
    1. Using Proper Normalization Techniques
      1. Normalizing Your Database
      2. First Normal Form: No Repeating Groups
      3. Second Normal Form: Dependent on the Whole Key
      4. Third Normal Form
      5. Denormalizing Your Database
    2. The SQL Server Optimizer
      1. Why You Need the Optimizer
      2. How the Optimizer Works
      3. Using Execution Plans to Figure Out What's Happening
      4. Client Statistics: Helping the Optimizer Do Its Job
    3. Using the Query Designer
      1. Creating a New Query
      2. Editing Your Query
      3. Exporting Your Query or Results
    4. Setting Query Options
      1. Configuring Query Options with Performance and Control Parameters
      2. Selecting Results Formatting Options
    5. Searching for Information
      1. Using AdventureWorks2008
      2. Retrieving Data from a Single Table
      3. Retrieving Data from Multiple Tables
      4. Filtering Information
    6. Organizing Query Results
      1. Using ORDER BY to Sort Your Results
      2. Grouping Results with GROUP BY
    7. Modifying Your Data
      1. Using DML Commands
      2. Adding Data to Your Database
      3. Modifying Data in your Database
      4. Removing Data from Your Database
    8. Taking Advantage of Views
      1. Tying Information Together with Views
      2. Creating a View
      3. Using a View
      4. Maintaining a View
      5. Deleting a View
    9. Advanced Query Topics
      1. Using Transactions to Protect Your Data
      2. Finding Information with Full-Text Search
      3. Understanding Outer Joins
      4. Querying XML Data
  9. Database Programming
    1. Understanding Transact-SQL
      1. Key Language Concepts
      2. Situations Where It Makes Sense to Use Transact-SQL
      3. Scenarios When It's Time to Use Another Programming Language
      4. Creating a Script
      5. Modifying a Script
    2. Stored Procedures and Functions
      1. Why You Need Stored Procedures and Functions
      2. Creating Stored Procedures and Functions
    3. Triggers
      1. DML Triggers: Letting Your Database Look After Itself
      2. DDL Triggers: Letting Your Server or Your Database Look After Itself
      3. Logon Triggers: Monitoring and Controlling Login Events
      4. Creating Triggers
      5. Maintaining Triggers
    4. Working with Visual Studio
      1. Introducing Visual Studio
      2. Navigating an SQL Server Database with Visual Studio
    5. Web Services
      1. Using Web Services to Distribute Data
      2. Using Web Services in Conjunction with SQL Server
    6. Developing Remote Applications
      1. Data Everywhere: Remote Applications to the Rescue!
      2. Determining When It Makes Sense to Access Data Remotely
      3. Using Linked Servers
    7. Advanced Development Topics
      1. Better Messaging through SQL Server Service Broker
      2. Automating Administration with SQL Server Management Objects
      3. Integrated Application Development with the .NET Framework
  10. Reporting Services
    1. Introduction to SQL Server Reporting Services
      1. What Reporting Services Provides to You and Your Users
      2. Understanding Reporting Services Components
      3. Installing Reporting Services
    2. Creating Reports with Report Builder
      1. Developing Reports Faster with Report Builder
      2. Designing a New Report
      3. Publishing Reports
      4. Maintaining Reports
    3. Creating Reports with Report Designer
      1. Generating Sophisticated Output with Report Designer
      2. Understanding Report Definition Language (RDL)
      3. Designing, Publishing, and Maintaining Reports
    4. Integrating Reports
      1. Tying Reports Together with SharePoint
      2. Using Familiar Microsoft Office Tools to View Reports
      3. Exposing Report Information with Web Services
  11. Analysis Services
    1. Introduction to SQL Server Analysis Services
      1. Introducing SQL Server Analysis Services (SSAS)
      2. Leveraging the Power of Multidimensional Data
      3. Choosing an Environment for Analysis Services
    2. Creating Business Intelligence Solutions with BIDS
      1. Understanding Business Intelligence
      2. Understanding Analysis Services Scripting Language (ASSL)
      3. Creating a SQL Server Analysis Project
      4. Exploring a SQL Server Analysis Services Project
    3. Data Mining and Maintaining Analysis Services Objects
      1. An Introduction to Data Mining
      2. Easy Integration with Business Intelligence Development Studio
      3. Creating New Scripts
      4. Managing Existing Analysis Services Objects
  12. Performance Tips and Tricks
    1. Working with the SQL Server Optimizer
      1. Understanding How an Optimizer Works
      2. Communicating with the Optimizer
      3. Helping Your Optimizer Help You
    2. Using Performance Monitoring Tools
      1. Laying the Right Foundation for Performance Monitoring
      2. Getting a Complete Picture with Windows Task Manager
      3. The Windows System Monitor
      4. Taking Advice from the Database Engine Tuning Advisor
      5. Viewing Graphical Performance Information with SQL Server Profiler
      6. Enforcing Control with the Resource Governor
    3. Data Access Strategies
      1. Setting a Good Foundation
      2. Using Indexes to Enhance Performance
      3. Designing High-Velocity Queries
      4. Changing Data Quickly
    4. Tuning SQL Server
      1. Tuning: The Last Resort for Improving Performance
      2. Memory and Processor Settings
      3. Disk Settings
      4. Communication Settings
  13. Database Administration
    1. Configuring SQL Server
      1. SQL Server Configuration Tools
      2. Adjusting Server Properties
      3. Generating Configuration Scripts
    2. Performing Major Administrative Tasks
      1. Controlling Database State
      2. Managing Disk Space
      3. Moving Databases
      4. Backing Up and Restoring Information
      5. Automating Things with Maintenance Plans
    3. Security: Keeping SQL Server Safe
      1. The Value of Security
      2. What Can You Secure?
      3. Who Can You Let Use Your Database?
      4. What Can You Let Users Do?
      5. Implementing Security
    4. Integration and Your Database
      1. Common Integration Challenges
      2. How SQL Server Integration Services (SSIS) Ties It All Together
      3. Using SQL Server Integration Services
    5. Replication
      1. Exploring the Publishing Metaphor
      2. Defining a Replication Publishing Model
      3. Configuring Replication
      4. Replicating between Hosts
    6. Spreading the Load with Partitioning
      1. Understanding SQL Server Partitioning
      2. Partitioning Key Terms and Concepts
      3. Setting up Partitioning in Your Environment
  14. Appendixes
    1. Ten Sources of Information on SQL Server 2008
      1. Microsoft SQL Server Web Site
      2. Microsoft SQL Server Developer Center
      3. Wikipedia
      4. Newsgroups
      5. Magazines
      6. User Groups
      7. Books
      8. Database Design Tools
      9. Administrative Tools
      10. Data Generation Tools
    2. Troubleshooting SQL Server 2008
      1. I Can't Install the Software!
      2. How Can I Administer My Database?
      3. My Data Is Messed Up!
      4. I Want to Automate Some Operations
      5. How Can I Simplify My Data?
      6. How Can I Load Information into SQL Server?
      7. My Data Is Unprotected!
      8. My Database Server Is Too Slow!
      9. Where Is AdventureWorks?
      10. Where Are My Reports?
    3. Glossary