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 XMLrelated 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. |
