Implementing a Microsoft SQL Server 2005
Database
Course
2779: Five days; Instructor-Led
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. |
Solutient
Corporation of Ohio
6133
Rockside Road, Suite 100 – Cleveland, OH
44131
FOR
MORE INFORMATION, CALL 216-654-0025