2779 - Implementing SQL Server 2005 Database Training Course

5 days.

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.

This course is intended for IT Professionals who want to become skilled on SQL Server 2005 product features and technologies for implementing a database.

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.

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.

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.

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 with FOR XML.
Shredding XML with OPENXML.
Using the xml Data Type.

Lab 3: Working with XML
Mapping Relational Data and XML.
Storing XML Natively in the Database.

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.

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.

Module 6: Creating Views

This module explains how to create views.

Lessons
Introduction to Views.
Creating and Managing Views.
Optimizing Performance with Views.

Lab 6: Creating Views
Creating Views.
Creating Indexed Views.
Creating Partitioned Views.

Module 7: Creating 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.

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.

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.