Implementing a Microsoft SQL Server 2005 Database
Introduction
This 40-hour course provides students with product knowledge and skills needed 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 wanting 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, triggers, and XML schemas.
|
| • |
Implement views.
|
| • |
Implement stored procedures and functions.
|
| • |
Implement managed code in the database.
|
| • |
Use Service Broker to build a messaging-based solution.
|
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 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 a Database
| • |
Creating a Database.
|
| • |
Creating Schemas.
|
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 data types.
|
| • |
Create 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.
|
| • |
Using the xml Data Type.
|
Lab 3: Working with XML
| • |
Mapping Relational Data and XML.
|
| • |
Storing XML Natively in the Database.
|
After completing this module, students will be able to:
| • |
Retrieve XML with FOR XML.
|
| • |
Shred XML with OPENXML.
|
| • |
Use the xml data type.
|
| • |
Use the methods of 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 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
This module explains how to implement constraints, triggers, and XML schemas.
Lessons
| • |
Data Integrity Overview.
|
| • |
Implementing Constraints.
|
| • |
Implementing Triggers.
|
| • |
Implementing XML Schemas.
|
Lab 5: Implementing Data Integrity
| • |
Creating Constraints.
|
| • |
Creating Triggers.
|
| • |
Implementing XML Schemas.
|
After completing this module, students will be able to:
| • |
Describe types of data integrity and options for enforcing it.
|
| • |
Implement constraints.
|
| • |
Implement triggers.
|
| • |
Implement XML schemas.
|
Module 6: Implementing Views
This module explains how to create views.
Lessons
| • |
Introduction to Views.
|
| • |
Creating and Managing Views.
|
| • |
Optimizing Performance by Using Views.
|
Lab 6: Creating 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.
|
| • |
Design views for performance.
|
Module 7: Implementing Stored Procedures and Functions
This module explains how to create stored procedures and functions.
Lessons
| • |
Implementing Stored Procedures.
|
| • |
Creating Parameterized Stored Procedures.
|
| • |
Creating Functions.
|
| • |
Handling Errors.
|
| • |
Controlling Execution Context.
|
Lab 7: Creating Stored Procedures and Functions
| • |
Creating Stored Procedures.
|
| • |
Creating Functions.
|
After completing this module, students will be able to:
| • |
Implement stored procedures.
|
| • |
Create parameterized stored procedures.
|
| • |
Handle errors in a stored procedure.
|
| • |
Implement Scalar Functions.
|
| • |
Create Table Valued Functions.
|
| • |
Control Execution Context.
|
Module 8: 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 8: 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 9: 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 9: Using Service Broker
| • |
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.
|
 Top of page
| 
