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.