Implementing a Microsoft SQL Server 2005 database (MOC 2779)

Course overview

This 5-day instructor-led course provides students with the knowledge and skills 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.

Prerequisites

In addition, it is recommended, but not required, that students have completed:

• Querying Microsoft SQL Server 2000 with Transact-SQL - MOC 2071 (H7081S)
• Maintaining a Microsoft SQL Server 2005 database - MOC 2780 (HC595S)
• e-learning offering

Audience

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

Ways to save

Next steps

• No Microsoft Certified Professional exams are associated with this course currently

Benefits to you

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

Course outline

Module 1: Creating Databases and Database Files

Lessons:

• Creating databases
• Creating filegroups
• Creating schemas
• Creating database snapshots

Lab: Creating Databases and Database Files

• Creating a Database
• Creating Schemas
• Creating a Database Snapshot

Module 2: Creating Data Types and Tables

Lessons:

• Creating data types
• Creating tables
• Creating partitioned tables

Lab: Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Creating Partitioned Tables

Module 3: Using XML

Lessons:

• Retrieving XML with FOR XML
• Shredding XML with OPENXML
• Introducing XQuery
• Using the xml Data Type

Lab: Using XML

• Mapping Relational Data and XML
• Storing XML Natively in the Database
• Using XQuery with xml Methods

Module 4: Creating and Tuning Indexes

Lessons:

• Planning indexes
• Creating indexes
• Optimizing indexes
• Creating XML indexes

Lab: Creating and Tuning Indexes

• Creating Indexes
• Tuning Indexes
• Creating XML Indexes

Module 5: Implementing Data Integrity

Lessons:

• Data integrity overview
• Implementing constraints

Lab: Implementing Data Integrity by Using Constraints

• Creating Constraints
• Disabling Constraints

Module 6: Implementing Data Integrity by Using Triggers and XML Schemas

Lessons:

• Implementing Triggers
• Implementing XML Schemas

Lab: Implementing Data Integrity by Using Triggers and XML Schemas

• Creating Triggers
• Implementing XML Schemas

Module 7: Implementing Views

Lessons:

• Introduction to Views
• Creating and Managing Views
• Optimizing Performance by Using Views

Lab: Implementing Views

• Creating Views
• Creating Indexed Views
• Creating Partitioned Views

Module 8: Implementing Stored Procedures

• Implementing Stored Procedures
• Creating Parameterized Stored Procedures
• Working With Execution Plans Handling Errors

Lab: Implementing Stored Procedures

• Creating Stored Procedures
• Working with Execution Plans

Module 9: Implementing Functions

Lessons:

• Creating and Using Functions
• Working with Functions
• Controlling Execution Context

Lab: Implementing Functions

• Creating Functions
• Controlling Execution Context

Module 10: Implementing Managed Code in the Database

Lessons:

• Introduction to the SQL Server Common Language Runtime
• Importing and Configuring Assemblies
• Creating Managed Database Objects

Lab: Implementing Managed Code in the Database

• Importing an Assembly
• Creating Managed Database Objects

Module 11: Managing Transactions and Locks

Lessons:

• Overview of Transactions and Locks
• Managing Transactions
• Understanding SQL Server Locking Architecture
• Managing Locks

Lab: Managing Transactions and Locks

• Using Transactions
• Managing Locks

Module 12: Using Service Broker

Lessons:

• Service Broker Overview
• Creating Service Broker Objects
• Sending and Receiving Messages

Lab: Using Service Broker (Optional)

• Creating Service Broker Objects
• Creating Service Broker Objects
• Implementing the Target Service

Module 13: Using Notification Services (Optional)

Lessons:

• Introduction to Notification Services
• Developing Notification Services Solutions