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