•  Implementing a Microsoft SQL Server 2005 Database

Create databases and database files.

Create data types and tables.

Use XML-related features in Microsoft SQL Server 2005.

Plan, create, and optimize indexes.

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

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

Implement views.

Implement stored procedures.

Implement functions.

Implement managed code in the database.

Manage transactions and locks.

Use Service Broker to build a messaging-based solution.

Use Notification Services to generate and send notifications.

Basic knowledge of the Microsoft Windows operating system and its core functionality.

Working knowledge of Transact-SQL.

Working knowledge of relational databases.

Some experience with database design.

Course 2778: Writing Queries Using Microsoft SQL Server 2005 Transact-SQL.

Course 2780: Maintaining a Microsoft SQL Server 2005 Database.

Creating Databases

Creating Filegroups

Creating Schemas

Creating Database Snapshots

Creating a Database

Creating Schemas

Creating a Database Snapshot

Create databases.

Create filegroups.

Create schemas.

Create database snapshots.

Creating Data Types

Creating Tables

Creating Partitioned Tables

Creating Data Types

Creating Tables

Creating Partitioned Tables

Create new data types.

Create new tables.

Create partitioned tables.

Retrieving XML by Using FOR XML

Shredding XML by Using OPENXML

Introducing XQuery

Using the xml Data Type

Mapping Relational Data and XML

Storing XML Natively in the Database

Using XQuery with xml Methods

Retrieve XML by using the FOR XML clause.

Shred XML by using the OPENXML function.

Use XQuery expressions.

Use the xml data type.

Planning Indexes

Creating Indexes

Optimizing Indexes

Creating XML Indexes

Creating Indexes

Tuning Indexes

Creating XML Indexes

Plan indexes.

Create indexes.

Optimize indexes.

Create XML indexes.

Data Integrity Overview

Implementing Constraints

Creating Constraints

Disabling Constraints

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

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

Implementing Triggers

Implementing XML Schemas

Creating Triggers

Implementing XML Schemas

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

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

Introduction to Views

Creating and Managing Views

Optimizing Performance by Using Views

Creating Views

Creating Indexed Views

Creating Partitioned Views

Describe the purpose of views.

Create and manage views.

Explain how to optimize query performance by using views.

Implementing Stored Procedures

Creating Parameterized Stored Procedures

Working With Execution Plans

Handling Errors

Creating Stored Procedures

Working With Execution Plans

Implement stored procedures.

Create parameterized stored procedures.

Work with execution plans.

Handle errors in stored procedures.

Creating and Using Functions

Working with Functions

Controlling Execution Context

Creating Functions

Controlling Execution Context

Create and use functions.

Work with functions.

Control execution context.

Introduction to the SQL Server Common Language Runtime

Importing and Configuring Assemblies

Creating Managed Database Objects

Importing an Assembly

Creating Managed Database Objects

Identify appropriate scenarios for managed code in the database.

Import and configure assemblies.

Create managed database objects.

Overview of Transactions and Locks

Managing Transactions

Understanding SQL Server Locking Architecture

Managing Locks

Using Transactions

Managing Locks

Describe how SQL Server 2005 transactions use locks.

Execute and cancel a transaction.

Describe concurrency issues and SQL Server 2005 locking mechanisms.

Manage locks.

Service Broker Overview

Creating Service Broker Objects

Sending and Receiving Messages

Creating Service Broker Objects

Implementing the Initiating Service

Implementing the Target Service

Describe Service Broker functionality and architecture.

Create Service Broker objects.

Send and receive Service Broker messages.

Introduction to Notification Services

Developing Notification Services Solutions

Describe how Notification Services operates.

Develop a Notification Services application.