Course 2779: Five days; Instructor-Led
Sign Up Now
Introduction
This five-day instructor-led 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. |
| • |
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 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. |
|
