M2779

Implementing a Microsoft SQL Server 2005 Database

Implementing a Microsoft SQL Server 2005

Database M2779

Course Title: Implementing a Microsoft SQL Server 2005 Database

Course Code: M2779
Version: B
Level: 200
Duration: 5 Days

Course Overview

The Implementing a Microsoft SQL Server 2005 Database M2779 instructor-led training course has been designed to provide candidates with the necessary skills and knowledge required 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.

Target Audience

Candidates who should consider attending the M2779 Microsoft training course are IT Professionals who want to become skilled on SQL Server 2005 product features and technologies for implementing a database.

Course Objectives

On successfully completing the M2779 course candidates 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 optimise 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 Prerequisites

Candidates attending the M2779 Microsoft training course should have a basic knowledge of the Microsoft Windows operating system and its core functionality. Candidates are also required to have a working knowledge of Transact-SQL and relational databases as well as some experience with database design.

In addition, candidates are also recommended to have previously attended and completed courses M2778: Writing Queries Using Microsoft SQL Server 2005 Transact-SQL and M2780: Maintaining a Microsoft SQL Server 2005 Database.

Testing and Certification

There are currently no exams or Microsoft Certification directly associated to this course however this course may assist in preparing candidates for other exams that are closely associated with this technology.

Course Content

Module 1: Creating Databases and Database Files

In this module candidates will learn how to create databases, filegroups, schemas, and database snapshots.

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

After completing this module, candidates will be able to:

• Create databases
• Create filegroups
• Create schemas
• Create database snapshots

Module 2: Creating Data Types and Tables

In this module candidates will learn how to create data types and tables. The module also describes how to create partitioned tables.

Lessons:

• Creating Data Types
• Creating Tables
• Creating Partitioned Tables

Lab: Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Creating Partitioned Tables

After completing this module, candidates will be able to:

• Create new data types
• Create new tables
• Create partitioned tables

Module 3: Using XML

In this module candidates will learn how to use the FOR XML clause and the OPENXML function and 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: Using XML

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

After completing this module, candidates 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

In this module candidates will learn how to plan, create, and optimise indexes as well as how to create XML indexes.

Lessons:

• Planning Indexes
• Creating Indexes
• Optimising Indexes
• Creating XML Indexes

Lab: Creating and Tuning Indexes

• Creating Indexes
• Tuning Indexes
• Creating XML Indexes

After completing this module, candidates will be able to:

• Plan indexes
• Create indexes
• Optimise indexes
• Create XML indexes

Module 5: Implementing Data Integrity by Using Constraints

In this module candidates will learn how to implement constraints. The module also provides an overview of data integrity.

Lessons:

• Data Integrity Overview
• Implementing Constraints

Lab: Implementing Data Integrity by Using Constraints

• Creating Constraints
• Disabling Constraints

After completing this module, candidates 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

In this module candidates will learn how to implement 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

After completing this module, candidates 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

In this module candidates will learn how to create views.

Lessons:

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

Lab: Implementing Views

• Creating Views
• Creating Indexed Views
• Creating Partitioned Views

After completing this module, candidates will be able to:

• Describe the purpose of views
• Create and manage views
• Explain how to optimise query performance by using views

Module 8: Implementing Stored Procedures

In this module candidates will learn how to create stored procedures and functions. The module also describes execution plans, plan caching, and query compilation.

Lessons:

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

Lab: Implementing Stored Procedures

• Creating Stored Procedures
• Working With Execution Plans

After completing this module, candidates will be able to:

• Implement stored procedures
• Create parameterised stored procedures
• Work with execution plans
• Handle errors in stored procedures

Module 9: Implementing Functions

In this module candidates will learn how to create functions and how to control the execution context.

Lessons:

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

Lab: Implementing Functions

• Creating Functions
• Controlling Execution Context

After completing this module, candidates will be able to:

• Create and use functions
• Work with functions
• Control execution context

Module 10: Implementing Managed Code in the Database

In this module candidates will learn how to implement managed database objects.

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

After completing this module, candidates 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

In this module candidates will learn how to use transactions and the SQL Server locking mechanisms to meet the performance and data integrity requirements of applications.

Lessons:

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

Lab: Managing Transactions and Locks

• Using Transactions
• Managing Locks

After completing this module, candidates 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

In this module candidates will learn how to build a messaging-based solution with 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

After completing this module, candidates 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)

In this module candidates will learn 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, candidates will be able to:

• Describe how Notification Services operates
• Develop a Notification Services application

$coverfor

PLEASE NOTE: Every effort has been made to ensure the accuracy of all information contained herein. IT Help and Support Centre Ltd makes no warranty expressed or implied with respect to accuracy of this information, including price, product editorials or product specifications. IT Help and Support Centre Ltd or its suppliers shall not be liable for incidental, consequential or special damages arising from, or as a result of, any electronic transmission or the accuracy of the information contained herin, even if IT Help and Support Centre Ltd has been advised of the possibility of such damages. Product and manufacturer names are used only for the purpose of identification.