MS2779 Implementing a Microsoft SQL Server 2005 Database

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.

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.

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.

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.

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.

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.

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.

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.

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.

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