MCDBA 2779B - Implementing a Microsoft SQL Server 2005 Database (Developer)
Duration: 5 Days
Course Price: $2495
Infor
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.

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.