SQL Server 2005: MCTS Bundle: MS2779 + MS2780

Course Overview

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

Skills Gained

This course will enable delegates 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. It is recommended, but not required, that students have completed an Introduction to SQL course or have equivalent skills.
• Working knowledge of relational databases.
• Some experience with database design.

Examinations

The combination of this course and MS2780A lead to Exam 70-431 : Microsoft SQL Server 2005-Implementation and Maintenance, which is at the Technology Specialist level.

Course Outline

Creating Databases and Database Files

• Creating Databases.
• Creating Filegroups.
• Creating Schemas.
• Creating Database Snapshots.

Creating Data Types and Tables

• Creating Data Types.
• Creating Tables.
• Creating Partitioned Tables.

Using XML

• Retrieving XML by Using FOR XML.
• Shredding XML by Using OPENXML.
• Using the xml Data Type.

Creating and Tuning Indexes

• Planning Indexes.
• Creating Indexes.
• Optimizing Indexes.
• Creating XML Indexes.

Implementing Data Integrity

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

Implementing Views

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

Implementing Stored Procedures and Functions

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

Implementing Managed Code in the Database

• Introduction to the SQL Server Common Language Runtime.
• Importing and Configuring Assemblies.
• Creating Managed Database Objects.

Using Service Broker

• Service Broker Overview.
• Creating Service Broker Objects.
• Sending and Receiving Messages.

Follow On Courses

• Maintaining a Microsoft SQL Server 2005 Database