You are in Training / Microsoft / SQL Server / Course MS2779 / Course Outline

MS2779 Implementing a Microsoft SQL Server 2005 Database Training and Courseware Course Outline

Module 1: Creating Databases and Database Files

This module explains how to create databases, filegroups, schemas, and database snapshots.

Lessons

  		•

    Creating Databases

  		•

    Creating Filegroups

  		•

    Creating Schemas

  		•

    Creating Database Snapshots

    Lab 1: Creating Databases and Database Files

  		•

    Creating a Database

  		•

    Creating Schemas

  		•

    Creating a Database Snapshot

    After completing this module, students will be able to:

  		•

    Create databases.

  		•

    Create filegroups.

  		•

    Create schemas.

  		•

    Create database snapshots.

    Module 2: Creating Data Types and Tables

    This module explains how to create data types and tables. It also describes how to create partitioned tables.

    Lessons

  		•

    Creating Data Types

  		•

    Creating Tables

  		•

    Creating Partitioned Tables

    Lab 2: Creating Data Types and Tables

  		•

    Creating Data Types

  		•

    Creating Tables

  		•

    Creating Partitioned Tables

    After completing this module, students will be able to:

  		•

    Create new data types.

  		•

    Create new tables.

  		•

    Create partitioned tables.

    Module 3: Using XML

    This module explains how to use the FOR XML clause and the OPENXML function. It also describes how to use the xml data type and its methods.

    Lessons

  		•

    Retrieving XML by Using FOR XML

  		•

    Shredding XML by Using OPENXML

  		•

    Introducing XQuery

  		•

    Using the xml Data Type

    Lab 3: Using XML

  		•

    Mapping Relational Data and XML

  		•

    Storing XML Natively in the Database

  		•

    Using XQuery with xml Methods

    After completing this module, students will be able to:

  		•

    Retrieve XML by using the FOR XML clause.

  		•

    Shred XML by using the OPENXML function.

  		•

    Use XQuery expressions.

  		•

    Use the xml data type.

    Module 4: Creating and Tuning Indexes

    This module explains how to plan, create, and optimize indexes. It also describes how to create XML indexes.

    Lessons

  		•

    Planning Indexes

  		•

    Creating Indexes

  		•

    Optimizing Indexes

  		•

    Creating XML Indexes

    Lab 4: Creating and Tuning Indexes

  		•

    Creating Indexes

  		•

    Tuning Indexes

  		•

    Creating XML Indexes

    After completing this module, students will be able to:

  		•

    Plan indexes.

  		•

    Create indexes.

  		•

    Optimize indexes.

  		•

    Create XML indexes.

    Module 5: Implementing Data Integrity by Using Constraints

    This module explains how to implement constraints and provides an overview of data integrity.

    Lessons

  		•

    Data Integrity Overview

  		•

    Implementing Constraints

    Lab 5: Implementing Data Integrity by Using Constraints

  		•

    Creating Constraints

  		•

    Disabling Constraints

    After completing this module, students will be able to:

  		•

    Describe the options for enforcing data integrity in SQL Server 2005.

  		•

    Implement data integrity in SQL Server 2005 databases by using constraints.

    Module 6: Implementing Data Integrity by Using Triggers and XML Schemas

    This module explains how to implement triggers and XML schemas.

    Lessons

  		•

    Implementing Triggers

  		•

    Implementing XML Schemas

    Lab 6: Implementing Data Integrity by Using Triggers and XML Schemas

  		•

    Creating Triggers

  		•

    Implementing XML Schemas

    After completing this module, students will be able to:

  		•

    Implement data integrity in SQL Server 2005 databases by using triggers.

  		•

    Implement data integrity in SQL Server 2005 databases by using XML schemas.

    Module 7: Implementing Views

    This module explains how to create views.

    Lessons

  		•

    Introduction to Views

  		•

    Creating and Managing Views

  		•

    Optimizing Performance by Using Views

    Lab 7: Implementing Views

  		•

    Creating Views

  		•

    Creating Indexed Views

  		•

    Creating Partitioned Views

    After completing this module, students will be able to:

  		•

    Describe the purpose of views.

  		•

    Create and manage views.

  		•

    Explain how to optimize query performance by using views.

    Module 8: Implementing Stored Procedures

    This module explains how to create stored procedures and functions. It also describes execution plans, plan caching, and query compilation.

    Lessons

  		•

    Implementing Stored Procedures

  		•

    Creating Parameterized Stored Procedures

  		•

    Working With Execution Plans

  		•

    Handling Errors

    Lab 8: Implementing Stored Procedures

  		•

    Creating Stored Procedures

  		•

    Working With Execution Plans

    After completing this module, students will be able to:

  		•

    Implement stored procedures.

  		•

    Create parameterized stored procedures.

  		•

    Work with execution plans.

  		•

    Handle errors in stored procedures.

    Module 9: Implementing Functions

    This module explains how to create functions. It also describes how to control the execution context.

    Lessons

  		•

    Creating and Using Functions

  		•

    Working with Functions

  		•

    Controlling Execution Context

    Lab 9: Implementing Functions

  		•

    Creating Functions

  		•

    Controlling Execution Context

    After completing this module, students will be able to:

  		•

    Create and use functions.

  		•

    Work with functions.

  		•

    Control execution context.

    Module 10: Implementing Managed Code in the Database

    This module explains how to implement managed database objects.

    Lessons

  		•

    Introduction to the SQL Server Common Language Runtime

  		•

    Importing and Configuring Assemblies

  		•

    Creating Managed Database Objects

    Lab 10: Implementing Managed Code in the Database

  		•

    Importing an Assembly

  		•

    Creating Managed Database Objects

    After completing this module, students will be able to:

  		•

    Identify appropriate scenarios for managed code in the database.

  		•

    Import and configure assemblies.

  		•

    Create managed database objects.

    Module 11: Managing Transactions and Locks

    This module explains how to use transactions and the SQL Server locking mechanisms to meet the performance and data integrity requirements of your applications.

    Lessons

  		•

    Overview of Transactions and Locks

  		•

    Managing Transactions

  		•

    Understanding SQL Server Locking Architecture

  		•

    Managing Locks

    Lab 11: Managing Transactions and Locks

  		•

    Using Transactions

  		•

    Managing Locks

    After completing this module, students will be able to:

  		•

    Describe how SQL Server 2005 transactions use locks.

  		•

    Execute and cancel a transaction.

  		•

    Describe concurrency issues and SQL Server 2005 locking mechanisms.

  		•

    Manage locks.

    Module 12: Using Service Broker

    This module explains how to build a messaging-based solution with Service Broker.

    Lessons

  		•

    Service Broker Overview

  		•

    Creating Service Broker Objects

  		•

    Sending and Receiving Messages

    Lab 12: Using Service Broker (Optional)

  		•

    Creating Service Broker Objects

  		•

    Implementing the Initiating Service

  		•

    Implementing the Target Service

    After completing this module, students will be able to:

  		•

    Describe Service Broker functionality and architecture.

  		•

    Create Service Broker objects.

  		•

    Send and receive Service Broker messages.

    Module 13: Using Notification Services (Optional)

    This module explains how to develop applications that generate and send timely messages to subscribers.

    Lessons

  		•

    Introduction to Notification Services

  		•

    Developing Notification Services Solutions

    After completing this module, students will be able to:

  		•

    Describe how Notification Services operates.

  		•

    Develop a Notification Services application.