Products >

Implementing a Microsoft SQL Server 2005 Database

Course 2779: Five days; Instructor-Led

Introduction

This five-day instructor-led course provides students with product knowledge and skills needed to implement a Microsoft SQL Server 2005 database. The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to implementing a database.

Audience

This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for implementing a database.

At Course Completion

After completing this course, students will be able to:

•

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.

Prerequisites

Before attending this course, students must have:

•

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.

In addition, it is recommended, but not required, that students have completed:

•

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

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.

We are glad to anwer questions regarding our business and course offerings.
Call us at (800) 453-5961 or Send an Email.

DynamicsEdge, Inc. All Rights Reserved. © 2010

[an error occurred while processing this directive]