Implementing a Microsoft SQL Server 2005 Database
Course 2779: Five days;
Instructor-Led
Course Fee:
$2495.00 (less 10% 4 - 7 day discount) = $2245.00
Course Time: 5 days, 9:00 am - 5:00 pm, or 10 evenings, 6pm - 9pm
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.
|
|
1.888.326.6886 
