Microsoft Course 6232 - Implementing a Microsoft SQL Server 2008 Database — A 5-Day Course

Course Synopsis

This five-day instructor-led M6232 course provides students with the knowledge and skills to implement a Microsoft SQL Server 2008 database. The course focuses on teaching individuals how to use SQL Server 2008 product features and tools related to implementing a database. This course is intended for IT Professionals who administer and maintain SQL Server databases.

Course Objectives

After completing this M6232 training course the student will be able to:

Create databases and database files
Create data types and tables
Use XML-related features in Microsoft SQL Server 2008
Plan, create, and optimize indexes
Implement data integrity in Microsoft SQL Server 2008 databases by using constraints
Implement data integrity in Microsoft SQL Server 2008 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

Intended Audience

This course is intended for IT Professionals who administer and maintain SQL Server databases.

Course Prerequisites

Before attending this course, students should have the following prerequisites:

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

Publicly scheduled dates, locations, and prices

London — £995 (+VAT)

19–23 Apr 2010
10–14 May 2010
17–21 May 2010
14–18 Jun 2010
12–16 Jul 2010
9–13 Aug 2010
13–17 Sep 2010
27 Sep–1 Oct 2010
4–8 Oct 2010
8–12 Nov 2010
29 Nov–3 Dec 2010
13–17 Dec 2010

Wokingham — £995 (+VAT)

22–26 Mar 2010
5–9 Jul 2010

Bristol — £995 (+VAT)

12–16 Apr 2010
6–10 Sep 2010
13–17 Dec 2010

Birmingham — £995 (+VAT)

12–16 Apr 2010
10–14 May 2010
26–30 Jul 2010
8–12 Nov 2010

Manchester — £995 (+VAT)

24–28 May 2010
12–16 Jul 2010
8–12 Nov 2010

Leeds — £995 (+VAT)

17–21 May 2010
24–28 May 2010
21–25 Jun 2010
13–17 Sep 2010
1–5 Nov 2010

Sunderland — £995 (+VAT)

10–14 May 2010
8–12 Nov 2010

Edinburgh — £995 (+VAT)

5–9 Apr 2010
16–20 Aug 2010

Glasgow — £995 (+VAT)

21–25 Jun 2010

Aberdeen — £995 (+VAT)

28 Jun–2 Jul 2010

Outline Course Contents

Creating Databases and Database Files

The students will learn one of the most fundamental tasks that a database developer must perform, the creation of a database and its major components, such as creating databases, setting database options, creating filegroups, schemas, and database snapshots.

Creating Databases
Creating Filegroups
Creating Schemas
Creating Database Snapshots

Creating Data Types and Tables

The students will learn about the system-supplied data types in SQL Server 2008. They will learn how to define custom Transact-SQL data types and how to create tables and how to use partitioned tables to organize data into multiple partitions.

Creating Data Types
Creating Tables
Creating Partitioned Tables

Creating and Tuning Indexes

The students will learn how to plan, create, and optimize indexes to attain optimal performance benefits.

Planning Indexes
Creating Indexes
Optimizing Indexes

Implementing Data Integrity by Using Constraints and Triggers

The students will learn about implementing data integrity in SQL Server 2008 by using constraints. They will also implement data integrity by using triggers.

Data Integrity Overview
Implementing Constraints
Implementing Triggers

Using XML

The students will learn how to work with XML, including use of the FOR XML clause, the OPENXML function, XQuery expressions, and the xml native data type. They will learn the considerations to be taken into account when creating XML indexes and the syntax used to create the XML indexes. They will also learn what XML schemas and XML schema collections are as well as how to use them to implement typed XML data.

Using the XML Data Type
Retrieving XML by Using FOR XML
Shredding XML by Using OPENXML
Creating XML Indexes
Implementing XML Schemas

Implementing Views

The students will be introduced to the different types of views available in Microsoft SQL Server 2008 which provide a convenient way to access data through a predefined query.

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

Implementing Stored Procedures

The students will learn the design and implementation of stored procedures to enforce business rules or data consistency, or to modify and maintain existing stored procedures written by other developers.

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

Implementing Functions

The students will learn the design and implementation of user-defined functions that enforce business rules or data consistency, or to modify and maintain existing functions written by other developers.

Creating and Using Functions
Working with Functions
Controlling Execution Context

Implementing Managed Code in the Database

The students will learn to use managed code to implement database objects, such as stored procedures, user-defined data types, user-defined functions, and triggers.

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

Managing Transactions and Locks

The students will learn to use transactions and SQL Server locking mechanisms to meet the performance and data integrity requirements of their applications.

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

Using Service Broker

The students will learn to use Service Broker to create secure, reliable, and scalable applications.

Service Broker Overview
Service Broker Objects
Sending and Receiving Messages