Implementing a Microsoft SQL Server 2008 Database (MOC 6232)

Special notes

Elements of this syllabus are subject to change

Course overview

This 5-day instructor-led 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

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

Audience

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

Ways to save

Course objectives

• 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

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

• (Level 200) Creating a Database
• (Level 200) Creating Schemas
• (Level 300) 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

• (Level 200): Creating Data Types
• (Level 200): Using New Date and Time Data Types
• (Level 200): Creating Tables
• (Level 300) Creating Partitioned Tables

Module 3: Creating and Tuning Indexes

Lessons:

• Planning Indexes
• Creating Indexes
• Optimizing Indexes

Lab: Creating and Tuning Indexes

• (Level 200) Creating Indexes
• (Level 300) Tuning Indexes

Module 4: Implementing Data Integrity by Using Constraints and Triggers

Lessons:

• Data Integrity Overview
• Implementing Constraints
• Implementing Triggers

Lab: Implementing Data Integrity by Using Constraints and Triggers

• (Level 200) Creating Constraints
• (Level 200) Disabling Constraints
• (Level 300) Creating Triggers

Module 5: Using XML

Lessons:

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

Lab: Using XML

• (Level 200) Mapping Relational Data and XML
• (Level 200) Storing XML Natively in the Database
• (Level 300) Using XQuery with XML Methods
• (Level 200) Create XML Indexes
• (Level 300) Implementing XML Schemas

Module 6: Implementing Views

Lessons:

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

Lab: Implementing Views

• (Level 200) Creating Views
• (Level 200) Creating Indexed Views
• (Level 200) Creating Partitioned Views

Module 7: Implementing Stored Procedures

Lessons:

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

Lab: Implementing Stored Procedures

• (Level 300) Creating Stored Procedures
• (Level 300) Working with Execution Plans

Module 8: Implementing Functions

Lessons:

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

Lab: Implementing Functions

• (Level 300) Creating Functions
• (Level 300) Controlling Execution Context

Module 9: 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

• (Level 300) Importing an Assembly
• (Level 300) Creating Managed Database Objects

Module 10: Managing Transactions and Locks

Lessons:

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

Lab: Managing Transactions and Locks

• (Level 300) Using Transactions
• (Level 300) Managing Locks
• (Level 300) Using Partition Locking

Module 11: Using Service Broker

Lessons:

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

Lab: Using Service Broker

• (Level 300) Creating Service Broker Objects
• (Level 300) Implementing the Initiating Service
• (Level 300) Implementing the Target Service