Moc 2779 - Implementing a Microsoft SQL Server 2005 Database

Introduction
Elements of this syllabus are subject to change.

This five-day instructor-led course provides students with the knowledge and skills 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 who want 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 2780: Maintaining a Microsoft SQL Server 2005 Database.

Modalidades: Presencial Las clases se imparten en castellano

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

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

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

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

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

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

Module 7: Implementing Views

This module explains how to create views.

Lessons

• Introduction to Views
• Creating and Managing Views
• Optimizing 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

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

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

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

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

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