2779: Implementing 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 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