Three days; Instructor-Led

Introduction

This three-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, triggers, and XML schemas.
•	Implement views.
•	Implement stored procedures and functions.
•	Implement managed code in the database.
•	Use Service Broker to build a messaging-based solution.

 

 

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 a Database

•	Creating a Database.
•	Creating Schemas.

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 data types.
•	Create 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.
•	Using the xml Data Type.

Lab 3: Working with XML

•	Mapping Relational Data and XML.
•	Storing XML Natively in the Database.

After completing this module, students will be able to:

•	Retrieve XML with FOR XML.
•	Shred XML with OPENXML.
•	Use the xml data type.
•	Use the methods of 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 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

This module explains how to implement constraints, triggers, and XML schemas.

Lessons

•	Data Integrity Overview.
•	Implementing Constraints.
•	Implementing Triggers.
•	Implementing XML Schemas.

Lab 5: Implementing Data Integrity

•	Creating Constraints.
•	Creating Triggers.
•	Implementing XML Schemas.

After completing this module, students will be able to:

•	Describe types of data integrity and options for enforcing it.
•	Implement constraints.
•	Implement triggers.
•	Implement XML schemas.

Module 6: Implementing Views

This module explains how to create views.

Lessons

•	Introduction to Views.
•	Creating and Managing Views.
•	Optimizing Performance by Using Views.

Lab 6: Creating 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.
•	Design views for performance.

Module 7: Implementing Stored Procedures and Functions

This module explains how to create stored procedures and functions.

Lessons

•	Implementing Stored Procedures.
•	Creating Parameterized Stored Procedures.
•	Creating Functions.
•	Handling Errors.
•	Controlling Execution Context.

Lab 7: Creating Stored Procedures and Functions

•	Creating Stored Procedures.
•	Creating Functions.

After completing this module, students will be able to:

•	Implement stored procedures.
•	Create parameterized stored procedures.
•	Handle errors in a stored procedure.
•	Implement Scalar Functions.
•	Create Table Valued Functions.
•	Control Execution Context.

Module 8: 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 8: 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 9: 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 9: Using Service Broker

•	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.