Course duration: 5 days

Cost: £1495 + Vat

About this Course

Elements of this syllabus are subject to change.

This five-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.

Audience Profile

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

At Course Completion
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.

Pre requisite

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.

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

· Course 2778: Writing Queries Using Microsoft SQL Server 2005 Transact-SQL.
· Course 2780: Maintaining a Microsoft SQL Server 2005 Database.

Course

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 : Creating Databases and Database Files
· Creating a Database
· Creating Schemas
· Creating a Database Snapshot
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 : Creating Data Types and Tables
· Creating Data Types
· Creating Tables
· Creating Partitioned Tables
After completing this module, students will be able to:

· Create new data types.
· Create new 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
· 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, students 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

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 : Creating and Tuning 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 by Using Constraints

This module explains how to implement constraints and 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, students 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
This module explains 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, students 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

This module explains how to create 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
After completing this module, students will be able to:

· Describe the purpose of views.
· Create and manage views.
· Explain how to optimize query performance by using views.

Module 8: Implementing Stored Procedures

This module explains how to create stored procedures and functions. It also describes execution plans, plan caching, and query compilation.
Lessons
· Implementing Stored Procedures
· Creating Parameterized Stored Procedures
· Working With Execution Plans
· Handling Errors
Lab : Implementing Stored Procedures
· Creating Stored Procedures
· Working With Execution Plans
After completing this module, students will be able to:

· Implement stored procedures.
· Create parameterized stored procedures.
· Work with execution plans.
· Handle errors in stored procedures.

Module 9: Implementing Functions

This module explains how to create functions. It also describes 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, students will be able to:

· Create and use functions.
· Work with functions.
· Control execution context.

Module 10: 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 : 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 11: Managing Transactions and Locks

This module explains how to use transactions and the SQL Server locking mechanisms to meet the performance and data integrity requirements of your 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, students 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

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 : Using Service Broker (Optional)
· Creating Service Broker Objects
· Creating Service Broker Objects
· 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.

Module 13: Using Notification Services (Optional)

This module explains 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, students will be able to:

· Describe how Notification Services operates.
· Develop a Notification Services application.